SQL

SQL Server local SQL authentication error logged in locally

SQL Server local SQL authentication error logged in locally

Highlights

  • 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 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.
  • 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.

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.

sqlloginerror01

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.
sqlloginerror02
sqlloginerror03

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.

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com and has over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, Brandon has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.