0

This question already has an answer here:

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 '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

marked as duplicate by Shawn Melton, Philᵀᴹ, RolandoMySQLDBA, Paul White, dezso Jan 29 '15 at 6:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new 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
  • This is a common error driven in many instances by even logging into SSMS and clicking the plus sign next to Databases, expanding a list of 84 databases when you only are authenticated to 1 will generate an error log of 83 failed authentications. The solution to this is to REVOKE VIEW ANY DATABASE from NordenDevel and your errors will go away. – Bryan Swan Feb 28 '17 at 3:22
0

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 , start the trace with default template using below 2 category of events:

1.Security Audit

2.Errors and Warning

a) From the Security Audit category (which is automatically listed in the default trace), keep the "Audit Login Failed" event.

b) From the Errors and Warnings category, only two events were chosen. First, the "ErrorLog" event - this would trap every instance the Error Log is accessed. The second and the most important event was the "User Error Message". This is the event that would capture the error returned to the client

For the columns, only five would be fine: TextData, ApplicationName, NTUserName, LoginName and SPID. Again, you may think the DatabaseID or DatabaseName columns would yield the required information

Finally, choose filter the NTUserName field to determine the account name from the Error Log.

After running trace you will see error message saying:

"Cannot open database requested by the login. The login failed."

From above error message you will get the correct DB name, for which login is throwing error 38.

Try capturing with trace and see if this works!

  • Nordenweb is the default database for this user and the trace gives the following user error message: "Cannot open database " NordenWeb" requested by the login. The login failed". 2 other logins with same default db give same result – YAOWSC Jan 29 '15 at 9:44
  • Can you share the screenshot of error message and access given to the login for default or same DB – KASQLDBA Jan 29 '15 at 9:53
  • Sorry about the delay - I was pulled away on another job for a few days. I would be glad to share the screenshot but cannot see how to do that here. Am I missing a file attachment method somewhere? – YAOWSC Feb 4 '15 at 12:56
  • @YAOWSC: you can use one drive live to save image and share the link here. – KASQLDBA Feb 4 '15 at 13:04
  • Thanks - silly of me! I hope this is what you need: onedrive.live.com/… – YAOWSC Feb 4 '15 at 18:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.