Error 18456 In Sql Server 2008 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 error 18456 sql server 2008 windows authentication the company Business Learn more about hiring developers or posting ads with us Database Administrators microsoft sql server error 18456 sql 2008 r2 Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who
Sql Server Error 18456 Severity 14 State 5
wish to improve their database skills and learn from others in 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
Microsoft Sql Server Error 18456 State 1
are voted up and rise to the top Login failed for user - Error 18456 - Severity 14, State 38 up vote 12 down vote favorite 4 Message that SQL Server Log File Viewer shows: Login failed for user [User] Error: 18456, Severity: 14, State 38 What it actually means: Failed to open the explicitly specified database My Question: Is there a list somewhere of all the variations of the 18456 errors (Login microsoft sql server error 18456 state 11 failed), for each combination of severity and state, with the helpful description text? I've had a Google but can't find anything other than specific combinations. sql-server errors logins share|improve this question asked Nov 30 '12 at 16:30 Pete Oakey 2841210 add a comment| 2 Answers 2 active oldest votes up vote 11 down vote State codes and their meaning. 1 'Account is locked out' 2 'User id is not valid' 3-4 'Undocumented' 5 'User id is not valid' 6 'Undocumented' 7 'The login being used is disabled' 8 'Incorrect password' 9 'Invalid password' 10 'Related to a SQL login being bound to Windows domain password policy enforcement. See KB925744.' 11-12 'Login valid but server access failed' 16 'Login valid, but not permissioned to use the target database' 18 'Password expired' 27 'Initial database could not be found' 38 'Login valid but database unavailable (or login not permissioned)' More detailed information is available in Aaron Bertrand's blog. share|improve this answer edited Dec 1 '12 at 13:27 answered Nov 30 '12 at 16:33 Pete Oakey 2841210 5 A LOT more details on these states (and several more states covered) in my blog post from last year: sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/… –Aaron Bertrand♦ Nov 30 '12 at 17:04 add a comment| up vote 0 down vote Here is what I found when I resolv
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted
Microsoft Sql Server 2005 Error 18456
the ability to create new threads on these forums. SQL Server Forums Profile microsoft sql server error 18456 login failed for user | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot microsoft sql server error 18456 linked server your Password? All Forums SQL Server 2008 Forums Other SQL Server 2008 Topics error: 18456, severity: 14, state: 38 Reply to Topic Printer Friendly Author Topic LawnMowerPros Starting Member USA 9 Posts http://dba.stackexchange.com/questions/29613/login-failed-for-user-error-18456-severity-14-state-38 Posted-03/06/2009: 23:06:31 I'm using SQL Server Express 2008 and I keep seeing this error (error: 18456, severity: 14, state: 38) in my log files. The error repeats many, many, many times and then the database goes into recovery mode. Can someone tell me what the error means?ThanksH and H Lawncare Equipmenthttp://www.LawnMowerPros.com guptam Posting Yak Master Canada 161 Posts Posted-03/06/2009: 23:25:40 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121202 State 38 means user doesn't have access to database it is trying to connect to.-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ LawnMowerPros Starting Member USA 9 Posts Posted-03/06/2009: 23:33:40 Thank you Mohit. I'm accessing the server using Classic ASP, what should I set the permission level to?Thanks Again.H and H Lawncare Equipmenthttp://www.LawnMowerPros.com guptam Posting Yak Master Canada 161 Posts Posted-03/07/2009: 02:46:54 What is your connection string? In your Connection string what ever database you have there user must have access to be able to at least read in that database. Thanks.-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ LawnMowerPros Starting Member USA 9 Posts Posted-03/07/2009: 18:04:50 My connection string is:Dim objConnSet objConn = Server.CreateObject("ADODB.Connection")objConn.ConnectionString = "Provider=SQLOLEDB;server=SERVER;database=DATABASE;uid=USERNAME;pwd=PASSWORD;"objConn.OpenI need read, write, delete. In the SQL Server Mangement screen I actually have permissions set to sysadmin. So I'm confused why I'd be getting the State 38 error.Thank you for your help.H and H Lawncare Equipmenthttp://www.LawnMowerPros.com guptam Posting Yak Master Canada 161 Posts Posted-03/07/2009: 18:29:25 Your "USERNAME" is sysadmin on server and you are getting th
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] https://justaprogrammer.net/2012/12/09/a-misleading-sql-error-message-error-18456-severity-14-state-38/ It took me a while to troubleshoot the error. The client's internal system administrator (who was quite sharp) only had to call me in in the first place because the error was a little http://www.ssistalk.com/2011/05/06/quick-tip-error-18456-severity-14-state-38/ 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 error 18456 learned that the user was 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. microsoft sql server This took me about an hour to 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
to see if a record exists Dynamic connection for file system task Ads Quick Tip: Error: 18456, Severity: 14, State: 38. SQL Server, SQL Server 2008 May 062011 When trying to investigate the SQL error, "Error: 18456, Severity: 14, State: 38." it can be frustrating when not a single log on the system will tell you what the actual problem is. In the case of state 38 (16 or 27 prior to SQL 2008) this error means the database specified in the client connection does not exist, or is offline. In a large shop with many various clients it can be hard to find out the source of the connection, so you're option is to use the tools at your disposal. The SQL Error Log does not help much at all. Here's what's in the SQL Error log: 2011-05-06 09:06:17.28 Logon Error: 18456, Severity: 14, State: 38. 2011-05-06 09:06:17.28 Logon Login failed for user ‘DOMAIN\ssrs.service'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147] The Windows Application log is not much help either. Login failed for user ‘DOMAIN\ssrs.service'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147] In other words, useless for troubleshooting. The way to obtain the database name the client is trying to connect to is to use SQL Server Profiler. To set up SQL Server Profiler, connect to the SQL instance where the error is occuring and then track the following events: Errors and Warnings: User Error Message Security Audit: Audit Login Failed For these two events, make sure you capture at least the following columns: ApplicationName, HostName, LoginName, SPID (required), StartTime, TextData (where the message text will be), Severity, State, ClientProcessID, and Error. Then run the trace. You'll see the following data under the "User Error Message" event when the login failure occurs: Cannot open database "DatabaseName" requested by the login. The login failed. For the "Audit Login Failed" event you'll see the following data: Login failed for user ‘BLUENE\ssrs.service'. Reason: Failed to open the explicitly specified database. [CLIENT: 192.168.0.147] Using this information (and the rest of the info in the other columns you selected), you should be able to go to the source and identify which process is trying to connect to the specified database and take appropriate action. If you're interested in the other states that error 18456 can generate, please visit fellow MVP Aaron Bertrand's page on this topic for a very nice listing. Attached is the SQL Profiler Trace Template for SQL 2008 instances