| We moved this page to our Documentation Portal. You can find the latest updates here. |
Issue
When logging in as the local user you can log in, but the user doesn't have sysadmin permissions
Environment
All OnApp versions
Resolution
Right after VM has been created, you have to run the script under the user Administrator
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
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
Cause
During provisioning of the Windows VM, OnApp runs sysprep, which resets the SID of VM. During installation of the SQL Server, it creates a set of local machine groups (e.g. MACHINE\SQLServer2005MSSQLUser$MACHINE$MSSQLSERVER) that are granted the rights throughout the OS and in SQL Server, so that the services can run. SQL Server stores the logins - and SID - for each of those groups in master.dbo.syslogins. The new SID wasn’t written with SQL, so after installing a new VM, the SID has to be updated.