ISV love for SYSADMIN

A coworker who is upgrading a piece of software asked me today about sysadmin role membership for the login used by said software. Of course, I asked the question as to why that was necessary. This is the response from the vendor’s document on the subject (emphasis added in places):

Why is SQL SysAdmin required for Crestron Fusion installs?

Answer ID: 5441   |    Access Level: Everyone   |    Updated 03/02/2015 03:31 PM

 

Installing Fusion requires SysAdmin privileges on the Microsoft SQL Server with only one exception.

It is important to backup the database nightly. For this reason, the Fusioninstaller creates a backup job.  SysAdmin is required for this. DBA’s can remove this backup job if there is another backup process in place.  We do this because our software must operate at any customer site including those that have no DBAs.

If the database is not backed up on some regular schedule, the transaction log will eventually fill the SQL server disk where it resides. Room number dependent and disk size dependent, this can happen very quickly. It is extremely difficult to recover from this condition if it happens.

The installer enables some trace flags, also requiring SysAdmin.  The trace flags help our application detect deadlocks and prevent lock escalation.

SysAdmin is required by the installer to run XP_CMDShell.  XP_CMDShell is used responsibly in this manner:

Other XP_ stored procedures that are used

1) Xp_fixeddrives is called to determine physical disk space and for determining the number of physical disks if the user decides to select different disks when creating the database

2) The registry is read using the query below because the create database statement requires a physical disk path

DECLARE @returnValue NVARCHAR(512)

DECLARE @returnData NVARCHAR(512)

DECLARE @returnLog NVARCHAR(512)

DECLARE @MasterDB NVARCHAR(512)

set @MasterDB = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N’master.mdf’, LOWER(physical_name)) – 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1)

EXEC xp_instance_regread

@rootkey = N’HKEY_LOCAL_MACHINE’,

@key = N’SOFTWARE\Microsoft\MSSQLServer\Setup’,

@value_name = N’SQLDataRoot’,

@value = @returnValue output

PRINT @returnValue

set @returnValue = ‘SOFTWARE\Microsoft’ + substring(@returnValue, charindex(‘\Microsoft’, @returnValue), len(@returnValue)) + ‘Server’

set @returnValue = ltrim(rtrim(@returnValue))

set @returnValue = replace (@returnValue, ‘\’, ‘\\’)

PRINT @returnValue

EXEC xp_instance_regread

@rootkey = N’HKEY_LOCAL_MACHINE’,

@key = @returnValue,

@value_name = N’DefaultData’,

@value = @returnData output

PRINT @returnData

EXEC xp_instance_regread

@rootkey = N’HKEY_LOCAL_MACHINE’,

@key = @returnValue,

@value_name = N’DefaultLog’,

@value = @returnlog output

PRINT @returnLog

select isnull(@returnData, @MasterDB) DBPath, isnull(@returnLog, @MasterDB) LOGPath

 

For Replication

1) Xp_cmdshell “sc qc {SQL Server Service Name” – this returns information regarding the SQL Server Service. We need to determine the account ID that is running SQL Server so we know who needs permission to the $REPL data share.

2) XP_CmdShell “sc qc {SQL Server Agent Service Name}” – same as 1

3) XP_CmdShell “ver” – returns the OS Version

4) XP_SubDirs returns all of the sub directories in the master path in search of the REPL data direcotry

5) XP_CMDShell is called to run CACLS or ICACLS – OS Dependent to guarantee that the SQL Agent Login Account and SQL Server Login account have sufficient permission on the REPL data directory.

 

Not For Replication

1) XP_CmdShell configures the SQL Agent to start automatically and starts it if it is stopped. This is necessary to run our SQL Agent jobs. Even in a single database model the nightly energy job is necessary.

 

Please note: Replication has been deprecated and is no longer supported in Fusion 10.x.

Cool! You just need to create some random backup jobs, turn on some trace flags, and enable xp_cmdshell? I guess I’m the jerk for asking the question…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s