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.