Guys, so I have run into a really weird situation with a development SQL Server local SQL authentication error logged in locally. ย Local SQL authentication succeeds if you login remotely via SQL Server Management Studio. ย However, using SSMS, the login fails logged in locally on the server itself.
Behavior/environment/tests so far:
- Server is Windows 2012 R2 with latest Windows updates and AV installed
- SQL is 2012 Standard edition with SP2
- The unique aspect of the build is that the server is a VMware VM on which Windows Server was loaded via a sysprepped, cloned operating system
- Problem seems to relate to sysprep
- To narrow down to sysprep issue, a clean Windows VM was loaded via ISO, patched, and AV’ed, SQL was installed and there are no issues logging in locally on the server using local SQL authentication
- The second test – a clean Windows VM was loaded, patched, AV’ed and then sysprepped (/oobe /generalize /shutdown). ย After the sysprep operation, the server was NOT cloned. ย Local SQL authentication fails logging in as SA or any other local SQL user.
- This test essentially eliminated the possibility of any issues with the VMware clone of the sysprepped server causing the issue
Other tests:
The weird thing is that the SQL server can successfully authenticate to SQL via commandlineย osql or via a UDL file in the same user session that it fails to login locally with SSMS. ย To eliminate an issue with SQL Management Studio in 2012 SP2, SQL 2014 Management tools were installed on the same server. ย SSMS 2014 had the same authentication issue that SSMS 2012 was having.
I want to pitch this out to you guys, have you seen issues with local SQL authentication on a Windows server that was sysprepped BEFORE SQL was installed? ย I would be very interested to know if anyone has seen this issue or something similar and any workarounds that were used to get past the issue.
About The Author
