SQL Server local SQL authentication error logged in locally
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.
