1

I configure a SQL Server instance and I have a problem with de SQL Server authentication when I try to connect locally (instance server).

When I connect to SQL remotely, the SQL Server authentication works fine, but when I try to connect locally, the server returns me the SQL Error 18456.

I don't know what's happened... I can connect remotely but not locally

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
jmunoa7
  • 185
  • 2
  • 15
  • What do you mean "locally"? What is the name of the instance and what is your connection string? Which protocols have you enabled? Is SQL authentication enabled? Did you provide the correct username/password in both cases? Where is the code? What did you try? One thing is absolutely certain - SQL authentication does work for any kind of connection. People would have noticed in the last 15 years if it didn't – Panagiotis Kanavos Sep 09 '15 at 07:17
  • Are you sure you *are* connecting to the proper instance? If, for example, your "remote" connection is to the machine name but the "local" to the instance name (or vice versa), you'll be connecting to the wrong database – Panagiotis Kanavos Sep 09 '15 at 07:21
  • Y trying to connect via SQL Server Management Studio. I have the SQL and Windows authentication enabled. The username/password are correct because remotely it's works. TCP/IP protocols are enabled. The server instance is STG-SQL – jmunoa7 Sep 09 '15 at 07:34
  • *Where* are you trying to connect - in both cases? What server/instance name are you using *in both cases*? Have you tried the *same* server name in both cases? *Have* you selected SQL Server authentication in the dialog box? Given that SQL Authentication *works*, you need to identify what's wrong. – Panagiotis Kanavos Sep 09 '15 at 07:41
  • What is the state number that accompanies the error number? The state number of the 18456 error usually reveals the reason for it. Check this [duplicate question](http://stackoverflow.com/questions/20923015/microsoft-sql-server-error-18456) or [Aaron Bertrand's detailed article](https://sqlblog.org/2020/07/28/troubleshooting-error-18456) for the meaning of the various states – Panagiotis Kanavos Sep 09 '15 at 07:46
  • @jmunoa7 did you solve this issue? I have a similar one and it is baffling. I am NOT typing the password incorrectly and there is no instance. – Maa421s Nov 29 '17 at 14:52

2 Answers2

1

If your instance is called SQLEXPRESS, then you need to use .\SQLEXPRESS or (local)\SQLEXPRESS or yourMachineName\SQLEXPRESS as your server name - if you have a named instance, you need to specify that name of the instance in your server name.

wiretext
  • 3,302
  • 14
  • 19
Kaushik Maheta
  • 1,741
  • 1
  • 18
  • 27
  • The error message isn't about connecting to an instance but a failed login, although the instance name may be missing from the connection string. There isn't enough information in the question to give an answer – Panagiotis Kanavos Sep 09 '15 at 07:20
0

There isn't enough information in this question to give an answer. The only certainty is that a local or remote connection doesn't really affect how authentication is performed.

Short Answer

Make sure you are connecting to the correct server. If you are, check SQL Server's log for the failed login message. The state number of the message explains the failure reason. In the SSMS Object Explorer, go to Management > SQL Server Logs > Current.

Long Answer

The fact that the error is 18456 means that you can connect to a server but the credentials are rejected.

One reason for this may be that you are connecting to the wrong server: when connecting to an instance, you must always provide the instance name, eg: .\INSTANCE or MACHINE\INSTANCE. If you click on the Technical Details enter image description herebutton in the SSMS error dialog, you'll see what server\instance you tried to connect to. For security reasons, no other information is disclosed in this message.

BTW, clicking on the help button of the dialog box will open a help page with a quick explanation of what might have happened.

To diagnose a real authentication problem, you need to find the full error message. The full message, including a state number that explains the reason, is stored in the server's Log. In SSMS, you'll find the log in Management > SQL Server Logs > Current.

In general, checking SQL Server's log should be the first action when an error is encountered that isn't directly related to a SQL statement.

The meaning of the various states is discussed in this similar SO question, described in this MSDN blog post and this detailed article by Aaron Bertrand.

A quick list of state numbers, copied from the SO question:

2, 5 = Invalid userid
6 = Attempt to use a Windows login name with SQL Authentication
7 = Login disabled and password mismatch
8 = Password mismatch
9 = Invalid password
11, 12 = Valid login but server access failure
13 = SQL Server service paused
18 = Change password required
Community
  • 1
  • 1
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236