none
SQL Server 2012 error 18456 when connecting using SQL Server non-sa account

    Question

  • Hi,

    We are testing SQL Server 2012 sp1 and able to install and everything working fine locally on the machine. We are able to connect using Windows account, sa account and other SQL Server accounts(username 'test'). But when we try to access SQL Server from other application machine using SQL Server login (username 'test') we are getting error 18456 but able to connect using 'sa' account. We are using mixed authentication and using correct login/password and don't see anything in SQL Server log file.

    What can be possible issue?

    • Moved by Tom Phillips Wednesday, April 03, 2013 9:45 PM Security question
    Wednesday, April 03, 2013 5:18 PM

Answers

  • That's probably correct - the reason the SQL error log won't be showing any login failures if it's configured to audit them is because the client is not even finding the remote server.

    But the error message is 18456, so the login attempt reaches some server.

    Furthermore, Harvinder says that login using sa is successful, it only fails for the test account.

    But, could it be - the server you are reaching have the same sa password as the other server? It's seems a little unlikely that Harvinder should have missed this, but just in case. First attempt to login as test from the remote server, then log in as sa. And run xp_readerrorlog. (There are of course other measures you could make to verify that you are on the expected server.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 08, 2013 9:37 PM
  • SQL Server browser is running so we were expecting it to handle the ports, anyhow switching the ports have solved the issue.

    Thanks a lot for all the help.

    Wednesday, April 10, 2013 7:11 PM

All replies

  • Hello,

    Please have a look at MSDN Troubleshooting: Login Failed for User 'x' how to troubleshoot this Problem.


    Olaf Helper

    Blog Xing

    Wednesday, April 03, 2013 5:24 PM
  • As I mentioned proeviously that we don't see any error messages in error log. Where else can we find more information?
    Wednesday, April 03, 2013 5:43 PM
  • Very strange that you don't see any the message in the error log. This is may be an insincere question: but you are sure that you are looking in the right errorlog, and on in the errorlog for a different instance?

    If you use xp_readerrorlog on the server in question, do you see any messages?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 03, 2013 9:43 PM
  • Hi,

    If you are not seeing login failed error messages in the SQL Server errorlog, your server may be configured to not audit them. In SQL Server management Studio, right click your server and go to Properties. On the Security page, there is a Logon Auditing option that specifies the auditing level for logons.

    This is true for SQL Server 2008, but I don't have access to a 2012 server right now. I am assuming that option is still there. Once you get a message in the errorlog, you can use the State value in the error to determine the cause of the failed logon.


    Thursday, April 04, 2013 5:08 AM
  • No errors shown by xp_readerrorlog  and SQL Server is configured to capture login audit events.

    Also I just noticed that when we are trying to connect locally by just using servername then it works fine but when I specify complete servername with domain like servername.test.cms then it won't work locally too and I checked the @@servername and it was just specifying servername so I modified it using sp_dropserver and sp_addserver and now it is showing complete name but still we are only able to access even locally only using servername and that may be the real root issue since we have to specify complete name from remote computer. What can be the possible issue or fix?

    Monday, April 08, 2013 3:19 PM
  • That's probably correct - the reason the SQL error log won't be showing any login failures if it's configured to audit them is because the client is not even finding the remote server.

    This will either be a network/firewall issue (assuming you can see/ping the server from the client) or the server is not configured to allow remote connections. The following article covers both:

    http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx


    ajmer dhariwal || eraofdata.com

    Monday, April 08, 2013 5:24 PM
  • That's probably correct - the reason the SQL error log won't be showing any login failures if it's configured to audit them is because the client is not even finding the remote server.

    But the error message is 18456, so the login attempt reaches some server.

    Furthermore, Harvinder says that login using sa is successful, it only fails for the test account.

    But, could it be - the server you are reaching have the same sa password as the other server? It's seems a little unlikely that Harvinder should have missed this, but just in case. First attempt to login as test from the remote server, then log in as sa. And run xp_readerrorlog. (There are of course other measures you could make to verify that you are on the expected server.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 08, 2013 9:37 PM
  • Yup - evidently my speed-reading is not as thorough as it should be!

    If sa can connect then it might just be an issue with e.g. the default database assigned to the test login not being present or accessible, but this would have been mentioned in the 18456 error that pops up.

    It might be worth tracing the connection attempt - if you want information on how to do this I've information on doing this using the (now rather old fashioned) SQL Profiler. If it does not get picked up by Profiler then, as Erland has suggested, the issue may be more related to which server you're actually connecting to.



    ajmer dhariwal || eraofdata.com


    Tuesday, April 09, 2013 11:26 AM
  • Thanks Erland and Ajmer. I think we are getting close so here is the story and I am sure you guys can point me to the solution:

    We installed SQL 2008 as names instance Old2008 that is using port 1433 and we are able to connect to this instance remotely and locally

    We install SQL 2012 as default instance that is using port 7122 and this is giving us issue. SA password is same for both the instances so when I was specifying servername.test.cms then infact somehow it is conneting to servername.test.cms\Old2008 even though it shows me that it is connected to servername.test.cms but databases were all that of Old2008 instance so we can say that I was never able to connect to servername.test.cms even using sa account.

    Now why is SQL Server connecting to servername.test.cms\Old2008 even when we are trying to connect to default instance, is there some setting or IP address that is configured incorrectly.

    Tuesday, April 09, 2013 6:27 PM
  • If I read that right, a named instance (servername.test.cms\Old2008) has been installed and configured to use the default instance port of 1433? Not really sure that's advisable and this would explain why connections to servername.test.cms may have been redirected to servername.test.cms\Old2008, especially if the SQL Browser service is not running and intercepting and redirecting the requests to the right instance on the right port number.

    Make sure the browser service is running and retry (with the relevant firewall exceptions on TCP and UPD listed in the blog post mentioned previously). If the browser service has always been running then just add the port number of the server you want to connect to in the servername box of the connection dialog, e.g. servername.test.cms,7122 without any backslashes/references to instance names (this basically does the job of the browser service and directs the connection to whichever instance is listening on that port); I have a post on verifying connectivity issues like this as well, but I won't spam this conversation!


    ajmer dhariwal || eraofdata.com


    Tuesday, April 09, 2013 6:50 PM
  • Yes, putting a named instance on the port normally used by the reserved instance is a recipe for confusion. I'm not going to say more as Ajmer's post has all information you need.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 09, 2013 9:27 PM
  • SQL Server browser is running so we were expecting it to handle the ports, anyhow switching the ports have solved the issue.

    Thanks a lot for all the help.

    Wednesday, April 10, 2013 7:11 PM