Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLCHECK & SSPICLIENT - Feature Request - Sanity check @@SERVERNAME is equivalent to the requested name #44

Open
jamesaepp opened this issue Sep 15, 2022 · 3 comments

Comments

@jamesaepp
Copy link

jamesaepp commented Sep 15, 2022

I have been banging my head against the wall for hours trying to figure out why SSPI connections to MS SQL servers suddenly started to fail.

From a blind guess troubleshooting from a completely different direction I found out that the @@SERVERNAME property of the MS SQL server/services/instance was different than what was being contacted in the client.

Knowing this would have saved me a lot of pain and it's something I believe the tools should be able to figure out. At the very least SQLCHECK -- SSPICLIENT may be a different story.

If you need clarification please let me know.

@Malcolm-Stewart
Copy link
Contributor

Malcolm-Stewart commented Sep 19, 2022

Hi James, if you are getting SSPI errors, then the SELECT @@ServerName would not run. In order to run this successfully, you would have had to modify your connection to not attempt Kerberos, e.g., use a SQL Login. That's beyond any of our tools' ability to make decisions like that.

Normally, we would [[Collect a Network Trace]] or [[Collect a SQL Driver BID Trace]] to see what the driver is really trying to do under the covers.

To dig a bit further, was the failure due to a SQL Alias or a hosts file entry, both of which will show in the SQLCheck report, or was it due to a DNS CNAME record, which will not show? Or some other reason? Understanding this can help us decide on an action.

Thanks

@jamesaepp
Copy link
Author

@Malcolm-Stewart :

Surely this would be possible to implement on SQLCHECK? If I run SQLCHECK locally on the DB server (or even SSMS locally) on the DB server everything is operational so I think it would be trivial to check the status of @@SERVERNAME and verify it against the computer names & SPNs of the computer object in AD.

As for the issue, it had nothing to do with hosts file, DNS, or aliases. It was simply that when the DB server was put into production and renamed in Windows from SERVERNAME-STAGED to SERVERNAME-PROD , the below steps weren't taken.

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver16

@Malcolm-Stewart
Copy link
Contributor

Good point. That could be a candidate for SSPIClient. SQLCheck does not make any connections to SQL Server at all.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants