Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

(0) Share

Report

Posted on by Community Member

When a Solomon SL user tries to run a report, we see the below errors in the SQL Server Error Log. This behavior has been explained to me as expected, but the reason for it has not. The users have local DSN's configured with SQL authentication, none of which work or seem to be used. I have a few questions I was hoping the community could help with.

1. The pattern of an empty string, AD account, empty string, and AD account is consistent (below). Again, it's also not how any of the client's DSN's are configured, they are all configured to use SQL authentication with accounts entered into the Login ID field. What is the reason for this behavior being reported in the SQL Error Log?

2. It was explained to me that Crystal reports in Dynamics SL will create the DSN's if it doesn't detect them on the client. This explanation doesn't make sense to me as they're not being used. Can someone explain this behavior?

3. The local DSN's configured on each users work station do not work (32 & 64). The SQL account configured in the DSN and the AD account displayed in the Error Log do not exist on the target database server. So each time a report is ran, broken DSN's are created and invalid users are attempted to authenticate to the database server. What is the reason for this behavior?

4. Ultimately the primary issue I'm trying to resolve are the error log entries in the database server error log. Is there a solution to preventing the error log entries other than turning off login auditing on the entire database server?

2016-03-29 11:49:20.300 Logon Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XX.XXX]

2016-03-29 11:49:20.300 Logon Error: 18456, Severity: 14, State: 58.

2016-03-29 11:49:20.290 Logon Login failed for user 'DOMAIN\USER'. Reason: Failed to open the explicitly specified database 'DATABASENAME'. [CLIENT: XX.XX.XX.XXX]

2016-03-29 11:49:20.290 Logon Error: 18456, Severity: 14, State: 38.

2016-03-29 11:49:20.280 Logon Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XX.XXX]

2016-03-29 11:49:20.280 Logon Error: 18456, Severity: 14, State: 58.

2016-03-29 11:49:20.270 Logon Login failed for user 'DOMAIN\USER'. Reason: Failed to open the explicitly specified database 'DATABASENAME'. [CLIENT: XX.XX.XX.XXX]

2016-03-29 11:49:20.270 Logon Error: 18456, Severity: 14, State: 38.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Cherie Kane 639 on at

Like (0)

Report

RE: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

Peter:

I was wondering if you ever got this figured out. I ran into this same error today when I was trying to back and restore the app and system database into a test environment.

I do see that SQL and Windows authentication is checked in the SQL Server properties.

Any help is appreciated.

Cherie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

Good Morning Cory. Thank you for your post, however the instance is configured for SQL & Windows authentication with Failed logins only. This is one of the first locations I checked after initially seeing the entries written to the SQL error log.

Peter

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

Hi Peter,

For the issue that you are seeing I am wondering what the SQL Server Security settings are? So if you open up SQL Server Management Studio, sign in, right click your server on the left hand side and select properties, switch to the Security page. What is the Server Authentication showing? This should be SQL Server and Windows Authentication mode.

Was this reply helpful? Yes No
Butch Adams 4,763 on at

Like (0)

Report

RE: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

Hi Peter,

The built in users are the Master80, the two long names beginning with 07... and e8... All three get used at some point during system usage.

"In there" is in the DSN when you go through the little wizard and it asks for the login info. My only point was that SL creates those DSN's for its own use, so don't be surprised if you can't make use of them. Just leave them alone once created.

My guess is that the "synchronyze" utility will recreate the built-in users and give them the appropriate permissions and cause your log entries to go away. It's only a guess because I've not seen the errors in your OP.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

Butch,

Thank you for your response. Few follow up questions for you.

"When the ROI.exe processor runs it uses those DSN with the built-in users that the Synchronize process will create."

What are the built in users? Are you referring to the Master80 SQL account, etc?

"The user that is saved in there when you look at it is not used. The system overwrites the information it needs both there and in the Crystal report to generate the results."

Where is 'in there'? Where is the user information is saved?

I found the below article describing the process you suggested. I reviewed the article but don't see how that would fix the faulty DSN issue and the Error Log entries being generated in SQL Server. I'm specifically referring to the portion of the article that describes what the utility does during the synchronization for "Windows Authenticated configuration". Which part of the article are you thinking will fix the Error Log entries?

www.boyerassoc.com/.../synchronize-ownership-security-dynamics-sl

Thanks again.

Peter

Was this reply helpful? Yes No
Butch Adams 4,763 on at

Like (0)

Report

RE: Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: XX.XX.XXX.XX]

Have you run the Synchronize All Ownership & Security process in the Database Maintenance utility? That might fix it.

When the Crystal reports are run, they create the DSN's if they do not exist. When the ROI.exe processor runs it uses those DSN with the built-in users that the Synchronize process will create. The user that is saved in there when you look at it is not used. The system overwrites the information it needs both there and in the Crystal report to generate the results.

When you run Synchronize, make sure everyone is out of the system.

I hope this helps.

...b

Was this reply helpful? Yes No