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.