Accessing Windows MS SQL on New Templates

Issue

A local user can log in but without sysadmin permissions.

Environment

All OnApp versions

Resolution

After a virtual server has been created, run the script as an administrator user:

• For MSSQL server:

  net stop MSSQLSERVER
  reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters" /v "SQLArg3" /t REG_SZ /d "-mSQLCMD" /f
  net start MSSQLSERVER
  sqlcmd -S. -Q "CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE= [master], DEFAULT_LANGUAGE=[us_english]"
  sqlcmd -S. -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'"
  net stop MSSQLSERVER
  reg delete "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters" /v "SQLArg3" /f
  net start MSSQLSERVER

  CODE

• For MSSQL Express:

  net stop MSSQL$SQLEXPRESS
  reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\Parameters" /v "SQLArg3" /t REG_SZ /d "-mSQLCMD" /f
  net start MSSQL$SQLEXPRESS
  sqlcmd -S.\SQLEXPRESS -Q "CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE= [master], DEFAULT_LANGUAGE=[us_english]"
  sqlcmd -S.\SQLEXPRESS -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'"
  net stop MSSQL$SQLEXPRESS
  reg delete "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\Parameters" /v "SQLArg3" /f
  net start MSSQL$SQLEXPRESS

  CODE

Cause

During provisioning of a Windows virtual server, OnApp runs sysprep, which resets the SID of the virtual server. The installation of SQL server creates a set of local machine groups (e.g., MACHINE\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER) that are granted the rights throughout the OS and on the SQL server for the services to be run. The SQL server stores the logins and SID for each of these groups in master.dbo.syslogins. As the new SID was not written with the SQL, the SID has to be updated after installing a new virtual server.