By: Svetlana Golovko | Updated: 2019-12-18 | Comments (1) | Related: > Install and Uninstall
Problem
We all know about the best practice of setting up SQL Server on a dedicated host, ideally SQL Server should not share the server with any other applications. But in real life sometimes there are scenarios when an application must be installed with SQL Server on the same machine. These scenarios usually include isolated applications configuration that require strict security or must be segregated from the rest of the environment. This might be also applicable to the applications that need to have SQL Server Express installed on the same server.
How can we lock down SQL Server? How do we make sure our server is secure and protected from external access?
Solution
It this tip we will provide recommendations for hardening the SQL Server installation for the configuration where SQL Server is only accessible by the application installed on the same server. In most cases this will be a standalone server that is not even joined to a domain. We will provide examples and scripts for the minimal permissions setup required for the databases backups and maintenance.
Pre-Installation Steps
By default, if you don’t create the folder for SQL Server in advance, the data folder(s) will have some of the permissions inherited from the parent folder. The local "Users" (COMPUTERNAME\Users) group will have read permissions to the folders:
Before we start the installation, we will create Data, Backups and other folders and make sure that only Administrators have access to these folders.
We will create the new folders and disable permissions inheritance first:
Convert permissions into explicit permissions when prompted and remove the local "Users" group’s permissions ("Read & execute" and "Special"):
SQL Server Service Accounts will be granted required permissions during the installation later, so we don’t need to do this now.
File System permissions required for the SQL Service Accounts could be found here.
SQL Server Installation
Start SQL Server installation as you usually do. Follow the installation prompts until "Feature Selection" screen.
On the Feature Selection screen make sure that only "Database Engine Services" is selected:
Do not select the regular features you usually install, such as: SQL Server Tools, Full-Text Search, etc.
Note: that the Full-Text might be required for your application or refer to your vendor’s documentation for the SQL Server features requirements.
Also, we assume that this is a simple application that only requires a database engine feature (no SSRS, SSAS, etc.).
We want to reduce the security surface area, so SQL Server Agent and SQL Server Browser will be disabled too. Any Scheduled Maintenance and Backup Tasks will run as Windows Scheduled Tasks that will be configured later:
Keep the default service accounts. Read this Microsoft’s article about service accounts and permissions required for the accounts.
If the application supports Windows Authentication – keep the default Authentication Mode (Windows).
Use the directory (or directories if you use multiple drives) that we have created before starting the installation:
Complete the installation.
Read this tip for the complete SQL Server 2017 Step by Step Installation Guide.
SQL Server Security Configuration
We will set up some of the security features here.
Disable all network protocols except "Shared Memory":
Validate in SQL Server Configuration Manager that all services except "SQL Server" are disabled:
Hide the Instance using SQL Server Configuration Manager. Read this tip about Hiding SQL Server Instances.
Make sure that Remote Access is disabled:
USE [master]
GO
EXEC sys.sp_configure N'remote access', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
Enable local password policies if you are setting up SQL Server on a standalone server. Domain joined servers should inherit the domain policies, so you can skip this step for the servers that are part of the Active Directory Domain. The password policies for the Domain are usually setup by your Systems Administrators.
On a Standalone server:
Start MMC Console
Click "File" > "Add/Remove Snap-in…"
Select "Group Policy Objects Editor", click "Add", confirm the "Local Computer" Group Policy Object selection:
Click "OK"
Under "Local Computer Policy" expand "Computer Configuration", then "Windows Settings", then "Security Settings", "Account Policies", "Password Policy"
Modify the policies on the right to enforce strong passwords:
Read more about local password policies configuration on Windows here. Read this article about SQL Server Password Policies.
Keep the MMC Console opened for the next steps.
Other Post Installation Steps
Install the latest patches and fixes (including Windows patches).
Set a strong password, rename and disable the "sa" login.
This tip has a security checklist for SQL Server. Make sure you reviewed the checklist and this security guide for any additional security configurations that might be required.
Maintenance Service Account Creation
We will create a local service account for running scheduled SQL Server Maintenance Tasks (updating statistics, backing up databases etc.). We are going to use the Computer Management Tool on our SQL Server for this:
This service account will run Windows Scheduled tasks. The user does not need to be member of any groups, but it must have "Log on as a batch job" local permissions in order to run Windows jobs.
Go back to the MMC Console to assign "Log on as a batch job" policy to the service account:
Click "Add User or Group…" button and add "SQLTaskTest" user that we have created earlier:
Maintenance Service Account Permissions on SQL Server and Databases
These are the minimum permissions required for the service account to run the databases backups and maintenance tasks:
USE [master]
GO
CREATE LOGIN [LOCALSRVNAME\SQLTaskTest] FROM WINDOWS DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [_Demo] -- for each database except tempdb
GO
CREATE USER [LOCALSRVNAME\SQLTaskTest] FOR LOGIN [LOCALSRVNAME\SQLTaskTest]
GO
ALTER ROLE db_backupoperator ADD MEMBER [LOCALSRVNAME\SQLTaskTest] –- to create backups
GO
GRANT ALTER ANY SCHEMA TO [LOCALSRVNAME\SQLTaskTest] –- to rebuild indexes
GO
GRANT IMPERSONATE ON USER::dbo TO [LOCALSRVNAME\SQLTaskTest] –- to update statistics
GO
The justification for these permissions is provided below for each script/task.
SQL Server Maintenance Scripts
We will need to run the following tasks:
- Databases Backups
- Indexes Maintenance
- Statistics Updates
- Databases Consistency Checks
Here are SQL Server scripts and executable OS script (*.bat file) that will run these SQL scripts:
Backup All SQL Server Databases Script
"BackupAllDBs.sql" script (replace the backup folder name with your value in this script):
SET NOCOUNT ON
SELECT GETDATE()
EXEC sys.sp_MSforeachdb
'IF (SELECT ''?'') <> ''tempdb''
BEGIN
BACKUP DATABASE [?]
TO DISK = N''D:\Backups\?.BAK''
WITH NOFORMAT, INIT, NAME = N''? – FULL DATABASE BACKUP''
END'
Here is a reference to the permissions required to backup the databases:
-
"BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles..."
We will grant minimum permissions required to create the backups to our service account (required for each database that will be backed up):
ALTER ROLE db_backupoperator ADD MEMBER [LOCALSRVNAME\SQLTaskTest] –- to create backups
GO
SQL Server Index Maintenance Script
"IndexesMaintenance.sql" script:
I used the index rebuild script from this tip. It rebuilds all indexes on all databases (or databases specified in the script). You could also create a more selective script that only rebuilds fragmented indexes, but we will use this script for demonstration of the minimal permissions setup.
Here is a reference to the permissions required to rebuild or reorganize the indexes:
-
"To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required."
We could potentially grant our service account "db_ddladmin" role, but this means that our user will be able to create and drop the tables. We will grant instead of this the following permissions:
GRANT ALTER ANY SCHEMA TO [LOCALSRVNAME\SQLTaskTest] –- to rebuild indexes
GO
We can test if the user can create or drop the tables or other objects. We will create a couple of test objects (as sysadmin or db_owner account):
USE [_Demo]
GO
CREATE TABLE dbo.Can_you_drop_table(
col1 INT NOT NULL,
CONSTRAINT PK_Can_you_drop_table PRIMARY KEY CLUSTERED
(col1 ASC)
GO
CREATE PROC dbo.Can_you_drop_procedure
AS
SELECT col1 FROM dbo.Can_you_drop_table
GO
Now we will login with the service account and run the following script:
USE [_Demo]
GO
PRINT 'Creating the table'
CREATE TABLE test_perm_tbl (c1 INT)
GO
PRINT 'Dropping the table'
DROP TABLE dbo.Can_you_drop_table
GO
PRINT 'Dropping the procedure'
DROP PROC dbo.Can_you_drop_procedure
GO
PRINT 'Altering the table'
ALTER TABLE dbo.Can_you_drop_table ADD col2 INT
GO
PRINT 'Rebuilding the indexes'
ALTER INDEX ALL ON dbo.Can_you_drop_table REBUILD
GO
Here are the results (with comments):
Creating the table Msg 262, Level 14, State 1, Line 2 CREATE TABLE permission denied in database '_Demo'. -- CANNOT create the table Dropping the table -- CAN delete the table Dropping the procedure Msg 3701, Level 11, State 5, Line 10 Cannot drop the procedure 'dbo.Can_you_drop_procedure', because it does not exist or you do not have permission. -- CANNOT delete the procedure Altering the table -- CAN change the table Rebuilding the indexes -- CAN rebuild the indexes
So, even though the user can modify and delete the tables it can’t delete other objects or create the new ones.
SQL Server Update Statistics Script
""UpdateStatistics.sql" script:
SET NOCOUNT ON
SELECT GETDATE()
EXECUTE AS USER='dbo'
Exec dbo.sp_updatestats
REVERT
Here is a reference to the article describing permissions required to update databases statistics:
-
"Requires membership in the sysadmin fixed server role, or ownership of the database (dbo)."
Instead of granting the service account sysadmin permissions we will use impersonation for our service account. It will impersonate the "dbo" user. In order for the service account to run "EXECUTE AS" statement we need to grant the following permissions to it:
USE [_Demo]
GO
GRANT IMPERSONATE ON USER::dbo TO [LOCALSRVNAME\SQLTaskTest] –- to update statistics
GO
SQL Server Database Consistency Checks
""CheckDBs.sql":
SET NOCOUNT ON
SELECT GETDATE()
EXEC dbo.usp_CheckDB
We have created a stored procedure to check the database in our demo database for our maintenance task demonstration:
USE [_Demo]
GO
CREATE PROC dbo.usp_CheckDB
AS
EXECUTE AS USER='dbo'
ALTER ROLE [db_owner] ADD MEMBER [LOCALSRVNAME\SQLTaskTest]
REVERT
DBCC CHECKDB WITH NO_INFOMSGS
EXECUTE AS USER='dbo'
ALTER ROLE [db_owner] DROP MEMBER [LOCALSRVNAME\SQLTaskTest]
REVERT
GO
Now we will grant the service account permission to execute the stored procedure:
USE [_Demo]
GO
GRANT EXECUTE ON dbo.usp_CheckDB TO [LOCALSRVNAME\SQLTaskTest]
GO
Here is a reference to the article describing permissions required to run DBCC CHECKDB:
-
"Requires membership in the sysadmin fixed server role or the db_owner fixed database role."
The Database Consistency Check could be only run by a user who is member of db_owner database role. The user has to be explicitly added to the role. The dbo user impersonation does not work in this:
EXECUTE AS USER='dbo'
DBCC CHECKDB WITH NO_INFOMSGS
REVERT
If the user is not member of db_owner role then there will be an error:
Msg 916, Level 14, State 1, Line 15
The server principal "sa" is not able to access the database "_Demo" under the current security context.
So, in our usp_CheckDB stored procedure we have the following steps:
- Impersonate the user as dbo
- Grant the maintenance service account "db_owner" role
- Run DBCC CHECKDB
- Remove the maintenance service account from the "db_owner" role
This is done through the stored procedure to make sure that the service account does not have db_owner or sysadmin permissions permanently.
Please note, that the scripts we use in this tip are very basic and are provided for demonstration purposes only.
The security setup in this tip might seem cumbersome, but this is done to demonstrate minimal permissions configuration for the service account.
Maintenance Service Account Permissions on Folders
We will have all maintenance and backup scripts under the new folder (for example, D:\DBScripts) and log (output) files under the "output" subfolder.
The security requirement is to make sure that the service account has an ability to write to the "output" folder and to make sure it cannot modify or write to the scripts folder (read only access to the "DBScripts" folder).
The backups folder will have to be secured as well. Read this tip about protecting the SQL Server Backup Folder.
DBScripts Folder
Go to the "DBScripts" folder’s Properties
Click on the "Security" tab
Click "Advanced"
Disable permissions inheritance
Remove any users except Administrators, SYSTEM, CREATOR OWNER (make sure you have access to the folder, we assume that we are members of the local Administrators group when we make these changes)
Add "SQL Task Test" Service Account, grant "Read & execute" permissions:
Check "Replace all child object permissions…" and click "OK".
Output Folder
Go to the "output" folder’s Properties
Click on "Security" tab
Click "Advanced"
Disable permissions inheritance
Remove any users except Administrators, SYSTEM, CREATOR OWNER (make sure you have access to the folder, we assume that we are members of the local Administrators group when we make these changes)
Add "SQL Task Test" Service Account, grant "Write" permissions:
Check "Replace all child object permissions…" and click "OK".
Setting up and Testing the Maintenance Tasks
The maintenance and backup scripts will be executed by running a single scheduled task. The task will execute the "Run_DBBackups.bat" batch file (replace the server names with your values in this script):
CD /D D:\DBScripts\
SQLCMD -E -S DEMOSQL1 -d master -i BackupAllDBs.sql -o D:\DBScripts\output\out_back.txt
SQLCMD -E -S DEMOSQL1 -d _Demo -i CheckDBs.sql -o D:\DBScripts\output\out_checkDB.txt
SQLCMD -E -S DEMOSQL1 -d _Demo -i IndexesMaintenance.sql -o D:\DBScripts\output\out_ind.txt
SQLCMD -E -S DEMOSQL1 -d _Demo -i UpdateStatistics.sql -o D:\DBScripts\output\out_stats.txt
Run the Windows Task Scheduler and click "Create Task…" under "Task Scheduler Library":
Select the service account created earlier as a user that will be running the task, select the other 2 options on a screenshot:
Under "Triggers" tab setup a schedule for the maintenance task:
Click "Actions" tab and enter the location and the name of the script (*.bat file):
Here is the final view of the action:
Click "OK" to save the task and enter the service account’s password when prompted:
Click "OK" on the next pop-up window:
We can ignore this message as we have already granted our service account required permissions.
Start the job manually to validate that it runs properly. Check the output folder to validate the results:
Next Steps
- Read other SQL Server Security Tips here.
- Read about different aspects of SQL Server Security in this article.
- Apply security settings from this tip to your SQL Server Express installations (SQL Server with Application on the same server configuration).
- Refer to the Center for Internet Security (CIS) for the SQL Server Security Benchmarks.
- Read about Extended Protection security feature here.
About the author
Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-12-18
