AnsweredAssumed Answered

SQL Authentication - Troubleshooting Failure

Question asked by Tony Asher on Nov 1, 2012
Latest reply on Sep 17, 2018 by Qsingh

I am attempting to get our SQL databases into Policy Compliance. This is an extra challenge compared to other assets I've setup in PC. I did read the MS_SQL_2005+8 Trusted Scanning Guide provided through the resource section. I have a MS SQL 2008 database running on a Windows 2008 Server asset.

 

I have an authentication record setup (named MS SQL) using 'Basic authentication', the correct username, the correct password, 'Authentication Type: Database'. I have entered the correct 'Instance Name' and 'Database Name'. I even verified these were correct by doing a query on the SQL database using Microsoft SQL Server Management Studio (SELECT DB_NAME() AS DataBaseName) . The odd duck is the port the database is using is 55194. I have also verified this by terminal service session to the Windows 2008 host, launching SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for %database name% --> TCP/IP --> IP Addresses Tab --> TCP Dynamic Ports = 55194 / TCP Port = blank. I also have the IP address in of the Windows 2008 host in the authentication record 'IP' space.

 

I also verified all of the authenication, database name and instance, and listening port with the resident SQL DBA.

 

When I perform a scan of the Asset Group "MS SQL Servers" (contains IP of Windows 2008 hosting the SQL database), then run an "Authentication Report" against the Asset Group I can see it successfully authenticated to the Windows 2008 host, and attempted to authenticate to the SQL 2008 database but failed. The message is:

HostInstanceStatusCause
10.10.x.x(windowsservername.domain.int,) Port 55194, Instance Name scrubbedname, Database Name master Failed MSSQL authentication was NOT successful on host 10.10.x.x, port 55194, instance scrubbedname, database master, auth mode, reason: connection failed


So far to troubleshoot I have telneted from a client machine to the Windows host on port 55194 and was successful. I have installed the SQL Server Management Studio on my client machine and using my authentication and server\database name I am able to successfully connect. (There is no field to enter port number with SQL Management Studio).

 

I'm tried searching for the SQL logs on the Windows 2008 server but I can't find them and I'm waiting for the DBA to get back to me and tell me where they are being stored so I can review them.

 

UPDATE: Working with the DBA we were able to find the authentication failure in the SQL logs:

2012-11-01 11:06:29.53Logon       Error: 18456, Severity: 14, State: 5.

2012-11-01 11:06:29.53Logon       Login failed for user '%username%'. Reason: Could not find a login matching the name provided.[CLIENT: QualysApplianceIP]

 

Anyone have this issue, or any advice I can try to continue troubleshooting?

 

UPDATE: Isn't it typical after you finally post a question - then you solve the issue? Working with the DBA we discovered "database used a different collation\sort order then the default so it is case sensitive". Bottom line - the username name in the authentication feild needed by be in CAPS. After changing it the authenication works successfully

 

Thanks. Maybe this will benefit someone else.

Outcomes