By: Edwin Sarmiento | Comments | Related: 1 | 2 | 3 | 4 | > Surface Area Configuration Manager
Problem
In a previous tip on SQL Server security settings using the Surface Area Configuration tool (SAC), you have seen how you can use the tool to configure security settings in your SQL Server 2005 instance. Since I manage multiple instances across multiple servers spanned across different geographical locations, is there a way for me to simply export the configuration I did for one instance using the Surface Area Configuration Tool and apply it to the other instances I manage?
Solution
The Surface Area Configuration tool comes with a command-line utility which you can use to export settings from one SQL Server 2005 instance and apply them to another. This makes it easy to create a configuration according to your corporate security policy on one instance and apply them on other instances as quickly as possible. The sac utility (sac.exe) is typically located in the %ProgramFiles%\Microsoft SQL Server\90\Shared folder or wherever you have configured your SQL Server 2005 binaries to be installed.
The syntax for using sac is as follows as mentioned in SQL Server 2005 Books Online
sac {in | out} filename [-S computer_name]
[-U SQL_login [-P SQL_ password]]
[-I instance_name ]
[-DE] [-AS] [-RS] [-IS] [-NS] [-AG] [-BS] [-FT] [-AD]
[-F] [-N] [-T] [-O]
[-H | -?]
I'll explain the most common parameters that you will probably use with this utility.
- in - this imports the surface area settings from a specified file and configure the instance, specified by instanceName, using those settings.
- out - this exports the surface area configuration settings from an instance to a file specified by filename
- filename - this is the full path of the file used when importing or exporting the surface area settings.
- -S computername - this specifies the name of a remote computer. If this parameter is not provided, the tool connects to the local computer.
- -U login - this specifies the SQL Server login to use for the connection to the database engine. If not specified, the tool will default to Windows Authentication
- -P password - this specifies the password for login. If this argument is not specified, the tool prompts for a password. If -P is specified at the end of the command without a value, it uses a null password.
- -I instanceName - this specifies the SQL Server instance. If this option is not specified, the tool connects to all SQL Server instances on the specified computer as provided in the -S parameter. For the default instance, the instance name is MSSQLServer.
- -DE - this specifies whether to import or export Database Engine settings.
Note that most of the parameters are similar to what we are familiar with using sqlcmd.exe or osql.exe. The output file generated by the tool is in an XML format that can be modified using any text editor, although this is not recommended at all.
Let's have a look at a few examples on how to use the sac utility.
Export all default instance settings
sac out configServer.out -S CONFIGSERVER -I MSSQLSERVER
Import feature settings to another SQL Server 2005 instance named SQLUAT
sac in configServer.out -S SQLUAT -F
I have only highlighted two examples here as SQL Server 2005 Books Online contains a lot of them. The main goal of this tip is to understand how we can use the sac utility and use it to automate the configuration of other SQL Server 2005 instances.
To automate this process here are the steps:
Step 1: Configure your settings using the Surface Area Configuration tool and then export this data to "configServer.out" as shown above.
Step 2: Create a file called SQL.txt that lists all of your SQL Server 2005 instances, such as the list below. Save this file as "SQL.txt".
SERVER\INSTANCE1 HOST1\INSTANCE1 HOST2\INSTANCE2 COMPUTER1\INSTANCE1 COMPUTER2\INSTANCE2 COMPUTER3\INSTANCE1
Step 3: We will read the SQL.txt file and generate a command to call the sac utility using VBScript using the code below. Save this VBScript file as "configureSAC.vbs".
Dim intSlash, strHostName, strInstanceName
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("D:\SQL.txt", 1)
Set objShell = CreateObject("Wscript.Shell")
Do Until objFile.AtEndOfStream
strLineItem = objFile.ReadLine
intSlash = Instr(strLineItem,"\")
strHostName = Left(strLineItem, intSlash-1)
strInstanceName = RIGHT(strLineItem, Len(strLineItem) - intSlash)
'Run the sac utility passing the parameters retrieved from the text file
objShell.Run("sac in configServer.out -S " & strHostName & " -I " & strInstanceName)
Loop
Set objFSO = Nothing
Set objFile = Nothing
Set objShell = Nothing
Save these files in the folder where the sac.exe is stored so you don't have to worry about the relative path of the execution of the command-line prompt. This will automate the configuration of the security settings for all the instances defined in the text file. This assumes that your Windows login credential has sysadmin privileges on the SQL Server 2005 instances where you are connecting to using the utility.
Step 4: Run the above VBScript file. This can be done by either double clicking on the VBScript file or you can run this from a command line.
While the Surface Area Configuration tool and the sac utility provides a means for us to configure security for our SQL Server 2005 instances, it has been removed in SQL Server 2008 in favor of Policy-based Management.
Next Steps
- Check out the sac utility in SQL Server Books Online.
- Check out these security tips on MSSQLTips.com.
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
