Error 18456 Severity 14 State 38
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers error 18456 severity 14 state 38 sql 2008 r2 or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database
Error 18456 Severity 14 State 38 Nt Authority System
Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in
Error 18456 Severity 14 State 38. Login Failed For User
the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Login failed for user - Error:
Error 18456 Severity 14 State 38. Sql Server 2008 R2
18456, Severity: 14, State: 38 [duplicate] up vote 0 down vote favorite This question already has an answer here: Login failed for user - Error 18456 - Severity 14, State 38 2 answers I am trying to run and ADODB command in MS Access 2012 to exec a stored proc in our SQL server 2008R2 database, but I keep getting error 38 and SQL profiler shows the following: Error: 18456, Severity: 14, State: 38. 2015-01-28 12:59:27.34 Logon Login failed for user error 18456 severity 14 state 11 'NordenDevel'. Reason: Failed to open the explicitly specified database. [CLIENT: 82.71.5.169] This user (NordenDevel) login works on this database (NordenWeb) in SQL Server Management Studio and in Visual Studio, so the database is working fine and I do not see how it could be a permission issue. Can anyone suggest what I could do to find out what's wrong? Many thanks, YAOWSC sql-server-2008-r2 security logins share|improve this question edited Jan 29 '15 at 15:27 marc_s 5,41632743 asked Jan 28 '15 at 18:42 YAOWSC 111 marked as duplicate by Shawn Melton, Philᵀᴹ, RolandoMySQLDBA, Paul White♦, dezso Jan 29 '15 at 6:01 This question was marked as an exact duplicate of an existing question. 1 Check that login's default database - I bet it's not NordenWeb. Or, somehow, Access is specifying a different database. –Aaron Bertrand♦ Jan 28 '15 at 18:44 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote As mentioned by Aaron, you're login 'say' X would be pointing to a different database rather than NordenWeb, when it throws error with state 38: You need to check the login for any other mapped databases. To find correct error you need to enable the trace when above error is created: To dig in deep do nslookup 82.71.5.169 from windows to find the host name, just to verify we are on the correct page: Now to track the correct database the login is throwing error , st
Related Tips: More > Error Logs Problem SQL Server Error Logs often show a message related to error 18456. Although it generally means a error 18456 severity 14 state 8 login attempt from a client connection has failed, different State numbers associated error 17187 severity 16 state 1 with the error can mean different reasons for the failure. One of the error States is 38, which error 18056 severity 20 state 29 was added with SQL Server 2008, means the database being accessed cannot be found or does not exist. Unfortunately the Error Log entry does not show what database it is, http://dba.stackexchange.com/questions/90445/login-failed-for-user-error-18456-severity-14-state-38 so in this tip we walk through how you can determine which database is causing the error message. Solution A few days ago I was looking through the Error Log of a database server and noticed a large number of the same error messages that looked like the following: 2011-12-15 11:22:08.76 Logon Error: 18456, Severity: 14, State: 38. 2011-12-15 11:22:08.76 Logon https://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/ Login failed for user '
I had to help a client out with an error that kept appearing in their event logs: Login failed for user ‘domain\user'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.25] It took https://justaprogrammer.net/2012/12/09/a-misleading-sql-error-message-error-18456-severity-14-state-38/ me a while to troubleshoot the error. The client's internal system administrator (who was http://www.sqlservercentral.com/Forums/Topic1345822-1526-1.aspx quite sharp) only had to call me in in the first place because the error was a little misleading. See the first thing I did when I saw that was audit login failures. In the trace, the database was listed as master. The user had full access to master. However, I later learned that the user was error 18456 switching from master to a non-existent database, which was triggering this error. I figured this out thanks to Sadequl Hussain‘s article, SQL Server Error 18456: Finding the Missing Databases. Sadequl explains in detail the how and the why. However, the take home is you need to trace for User Error Message to get the message that tells you what database you are connecting to. This took me about an hour to error 18456 severity solve. Honestly, it was a bit humbling of an experience. It took me an hour to figure out something a full time senior DBA would probably be able to solve in 15 minutes. However, I'll probably be able to solve this error in 15 minutes myself go forward. Finally, the fact that it took me a while to find this one blog article that explained what the issue actually was proves how dependent I've become upon google. Tagged Microsoft SQL Server Post navigation The #MongoHelp twitter manifestoAnnouncing ILRepack-BuildTasks Search for: Recent Posts Giving back to #sqlfamily Microsoft, please open source sqlcmd, Sqlps, SMO, and LogParser The case for open sourcing the SQL Saturday Website Split testing using nginx proxy cache Creating a minimally viable Centos instance for SSH X11 Forwarding Tags#SQLAmtrak #sqlfamily .Net 2010 year in review Async Workflow Atlantis Interactive centos chocolatey command line console f# farmanager firing fsharpx git github HowTo javascript jQuery meta Microsoft Microsoft SQL Server mongodb MSBuild MySQL OLPC Open Source OSS path php PlaneDisaster.NET poshrunner PowerShell project euler ReactOS RedGate SQL SQL Saturday SQL Saturday 121 SVG System Administration Team Foundation Server Visual Studio WCF windows internals study group Archives March 2015 April 2014 November 2013 May 2013 January 2013 December 2012 November 2012 Octobe
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » Security (SS2K8) » Error: 18456, Severity: 14, State:38 Error: 18456, Severity: 14, State:38 Rate Topic Display Mode Topic Options Author Message adb2303adb2303 Posted Thursday, August 16, 2012 4:56 AM SSC Eights! Group: General Forum Members Last Login: Today @ 8:58 AM Points: 943, Visits: 2,816 Hi,I've just moved all my SQL Server 2005 databases to a SQL Server 2008 R2 server with the same domain. I am 100% confident that all databases are present and accounted for. I used sp_help_revlogin to get the logins from 2005 server to the 2008 R2 server. All logins were created successfully. I then ran the following:ALTER USER [MYDOMAIN\MYACCOUNT] WITH LOGIN = [MYDOMAIN\MYACCOUNT]The problem is, I am getting the following error in the SQL Server Error log:Login failed for user 'MYDOMAIN\MYACCOUNT'. Reason: Failed to open the explicitly specified database [CLIENT:XX.XX.XX.XXX]Error: 18456, Severity: 14, State: 38I get this error even if I give the give the domain account sysadmin privileges on this box (which it doesn't have normally). I query sys.dm_os_ring_buffers and I can see several RING_BUFFER_SECURITY_ERROR errors, with ErrorCode: 0x534, APIName: LookupAccountSID. This, as far as I know, is a problem resolving the login details against AD. Then why does the very same account work perfectly fine on the same server in all the databases that it's been permissioned for?Any help greatly appreciated.Andrew Post #1345822 adb2303adb2303 Posted Thursday, August 16, 2012 7:29 AM SSC Eights! Group: General Forum Members Last Login: Today @ 8:58 AM Points: 943, Visits: 2,816 sp_validatelogins doesn't return anything either Post #1345952 anthony.greenanthony.green Posted Thursday, August 16, 2012 7:31 AM SSCertifiable Group: General Forum Members Last Login: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067 Run a trace for failed logins and see what database the failure is on.Then check to see if the DB exists or if the user has access to the DB. Want an answer fast? Try hereHow to post data/code for the best help - Jeff ModenWhen a question, really isn't a question - Jeff SmithNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawCrossTabs-Part1 & Part2 - Jeff ModenSQL Server Backup, Integrity Check,