error: 18456, severity: 14, state: 38

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

error: 18456, severity: 14, state: 38

Author

Topic

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-06 : 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?

Thanks

H and H Lawncare Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 23:25:40

State 38 means user doesn't have access to database it is trying to connect to.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-06 : 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 Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-07 : 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. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-07 : 18:04:50

My connection string is:
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB;server=SERVER;database=DATABASE;uid=USERNAME;pwd=PASSWORD;"
objConn.Open

I 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 Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-07 : 18:29:25

Your "USERNAME" is sysadmin on server and you are getting that error? Hmm..

Can you please confirm that with ..

SELECT SP1.name AS LoginName, SP2.name AS RoleName
FROM sys.server_role_members SRM,
sys.server_principals SP1,
sys.server_principals SP2
WHERE SRM.member_principal_id = SP1.principal_id
AND SRM.role_principal_id = SP2.principal_id

? Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-09 : 00:38:40

All LoginNames return sysadmin as the RoleNames.

What does that mean?

H and H Lawncare Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 03:51:08

Everyone in your server is defined as Sysadmin, which from security point is scary. But it's express, so probably not a major concern. But if you plan to use it for production, I would recommend fixing that. So back to your issue the login name in your connection string does it show up in that list belonging to SYSADMIN also?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-09 : 13:12:06

Yes, all users have SYSADMINpermission. This is not in production yet. When I switch to production what should I set the permissions to?

Yes the login name from the connection string shows up with SYSADMIN permissions.

H and H Lawncare Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 14:17:51

Permission on server level should be just public nothing more. On the database level db_datawriter and db_datareader. Since your permission are unlimited on server level we can rule out permission issue on database level. But is your database alright? You said "database goes into recovery mode"; is there automatic backups or restores running on the system? Are there any errors in the log about corruption?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-09 : 18:49:31

Looking in the log file I see no real corruption related errors. I just see this over and over again.

03/06/2009 07:04:36,Logon,Unknown,Login failed for user 'USERNAME'. Reason: Failed to open the explicitly specified database. [CLIENT: <named pipe>]
03/06/2009 07:04:36,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
03/06/2009 07:04:33,spid53,Unknown,Starting up database 'ReportServer$SQLEXPRESSTempDB'.
03/06/2009 07:04:09,spid51,Unknown,Starting up database 'DATBASE'.

Sometime it starts the database several times in a row and sometimes it starts the TempDB several times in a row. Occasionally I see COMPATIBILITY_LEVEL to 100 for database.

I do believe the server is setup for automatic backups but not restores.

Thank you.

H and H Lawncare Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 00:46:20

database is not active ... Starting up? Multiple times?

Make sure database are not set to auto close. If you right click on the database and goto properties, check for Auto Close; set it to false.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-11 : 01:00:07

Auto close was set to true. Do you think this will cure all my problems?

Thank you so much for taking the time to help.

H and H Lawncare Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-11 : 01:07:02

Well it can be problem because it takes few seconds for database to close and open; if your connections are coming in faster then it can open up maybe that is why it is failing. Did you ever see this?

Failed Login...
Opening DB..
Closing DB..
Failed Login
Opening DB.
no errors.....
Closing DB.

Basically the connection is trying to get through but because SQL Server has to bring the database online and rollforward or rollback transaction takes few seconds depending on all the work it has to do. I have seen it cause issues... Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

LawnMowerPros
Starting Member

9 Posts

Posted - 2009-03-12 : 08:11:47

I'm not sure if it is because I'm using SQL Express but it never states that it closes the database. Here is what I see in the log file.

Starting DB
Starting DB
Starting DB
Starting DB
Failed Login

It seems to work fine when there are only a few queries (1 every minute). But when the queries get heavy (10 per minute) I start to see the failed login.

I think this could be the solution to my problem. I'll give a go and see what happens.

Thank you for taking the time to help me.

H and H Lawncare Equipment
http://www.LawnMowerPros.com

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-12 : 08:59:10

Hmmm I am trying to remember if there is closing DB mark .. basically if you have that Auto Close set to true as soon as the transaction finishes it closes the DB and unloads it from SQL Server memory. So the next query it has to reload it, so I think if you set that auto close to false you should be good.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

tan00001
Starting Member

1 Post

Posted - 2009-09-30 : 23:53:00

We had the same type of error messages in our SQL Server log: "Login failed..., error: 18456, severity: 14, state: 38). It turned out to be our report server trying to connect to the SQL Server and trying to access its database with an incorrect name. Once we corrected the database name in the "Report Server Configuration Manager", the problem went away.

wgw
Starting Member

1 Post

Posted - 2010-11-23 : 21:43:03

I have the same error;what can i do now ?
i was confused if that the default database with the "sa".

and i have the other problem :the sqlserver 2008 was installed on a win2008server;and iis was installed a win2003server,iis need to connect the sqlserver2008,and all is running;i watch the network about two server;the win2003server was received a lot of data in one second,and the win2008server was sended the same data in one second; there is about 100 Mbit

but i can not find the problem.so what can i do?

POTG
Starting Member

1 Post

Posted - 2011-05-18 : 08:20:31

quote:
Originally posted by wgw

I have the same error;what can i do now ?
i was confused if that the default database with the "sa".

and i have the other problem :the sqlserver 2008 was installed on a win2008server;and iis was installed a win2003server,iis need to connect the sqlserver2008,and all is running;i watch the network about two server;the win2003server was received a lot of data in one second,and the win2008server was sended the same data in one second; there is about 100 Mbit

but i can not find the problem.so what can i do?

Been searching all kinds of forums without much of a solution from anyone.

Well, one of my developers came to me yesterday with this problem. It was tricky, because they had code deployed to testers and the code creates connection strings dynamically. The application worked fine for some, but for others it did not and the error was the same as others have listed here. The error logs, by default, give you that information. If it can do that, why not go all the way and provide what database it is that it thinks you're trying to connect to within that very same error message - seems like such a simple thing, doesn't it? Anyway, I tried a few SQL profiler traces, but to no avail until I looked at the simple event class, "User Error Message". When I set that and filtered it to my login for a test (filtered because we have so many other things coming through the server), the TextData for profiler showed exactly what database it thought I was trying to connect to and it was not what he expected. Somehow the dbname is getting mangled in the code. The developer still does not know why the code tries to connect to a database that does not exist, but at least he knows that it's not a "database problem" as developers like to call them :) Without that info, he'd still be relying on me to "find the problem".

I hope that little hint for diagnosing the problem helps someone else in the future although I realize that this can result from a number of issues.

crk
Starting Member

2 Posts

Posted - 2012-04-19 : 05:36:30

quote:
Originally posted by LawnMowerPros
03/06/2009 07:04:09,spid51,Unknown,Starting up database 'DATBASE'.

quote:
Originally posted by LawnMowerPros
objConn.ConnectionString = "Provider=SQLOLEDB;server=SERVER;database=DATABASE;uid=USERNAME;pwd=PASSWORD;"

Maybe the message was correct cause DATABASE does not exist, use DATBASE instead or rename the database.

crk
Starting Member

2 Posts

Posted - 2012-04-19 : 05:44:35

I got "error: 18456, severity: 14, state: 38" while connecting to a database at SQL Server Express 2008 with ADO 2.8. The error occured in a test program that connects and disconnects very often (connect - test 1 - disconnect, connect - test 2 - disconnect, ...). Every 10th or so connection failed with this error message. My solution was to catch the exception and to connect again immediately, that second try always works.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources