0

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

2
  • 1
    Check that login's default database - I bet it's not NordenWeb. Or, somehow, Access is specifying a different database. Jan 28, 2015 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
    Feb 28, 2017 at 3:22

1 Answer 1

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!

7
  • 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, 2015 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, 2015 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, 2015 at 12:56
  • @YAOWSC: you can use one drive live to save image and share the link here.
    – KASQLDBA
    Feb 4, 2015 at 13:04
  • Thanks - silly of me! I hope this is what you need: onedrive.live.com/…
    – YAOWSC
    Feb 4, 2015 at 18:07

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