SQL Server Security Best Practices for an Application Installed on SQL Server


By:   |   Updated: 2019-12-18   |   Comments (1)   |   Related: More > 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:

Data folder

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:

Advanced Security Settings for DATA folder

Convert permissions into explicit permissions when prompted and remove the local "Users" group’s permissions ("Read & execute" and "Special"):

Convert permissions into explicit permissions

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:

Installation - select features

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:

Installation - services configuration

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:

Installation - Data Directories

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":

Network Protocols Configuration

Validate in SQL Server Configuration Manager that all services except "SQL Server" are disabled:

Services state and startup mode validation

Hide the Instance using SQL Server Configuration Manager. Read this tip about Hiding SQL Server Instances.

Hide Instance

 

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:

MMC Console - Group Policy Objects Editor

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:

Password Policy settings

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:

Service Account creation

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:

Granting Log on as a batch job rights

Click "Add User or Group…" button and add "SQLTaskTest" user that we have created earlier:

Add service account to the policy

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:

DBScripts folder content

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:

DBScripts folder

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:

Output folder

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":

Create the new task

Select the service account created earlier as a user that will be running the task, select the other 2 options on a screenshot:

Task

Under "Triggers" tab setup a schedule for the maintenance task:

Edit Trigger, add schedule

Click "Actions" tab and enter the location and the name of the script (*.bat file):

Edit Action, add programm

Here is the final view of the action:

Final Actions view

Click "OK" to save the task and enter the service account’s password when prompted:

Service account password pop-up

Click "OK" on the next pop-up window:

Log on as batch confirmation

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:

Output files
Next Steps


Last Updated: 2019-12-18


get scripts

next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips
Related Resources





Comments For This Article




Wednesday, June 10, 2020 - 8:13:33 AM - Robert Back To Top (85916)

Hi Svetlana,

In the beginning of your article u state:

"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."

Could u elaborate on that? It's not that obvious I guess:  https://serverfault.com/questions/92647/windows-web-server-and-sql-server-on-same-dedicated-server

grt!

 



download





Recommended Reading

Cleanly Uninstalling Stubborn SQL Server Components

SQL Server install error Wait on the Database Engine Recovery Handle Failed

Fix SQL Server Installation issue Wait on the Database Engine recovery handle failed with Single User Mode Login Option

SQL Server Installation Best Practices

Visual Studio 2019 Configuration for the Production DBA








get free sql tips
agree to terms


Learn more about SQL Server tools