Security in SQL Server – Part 3

The estimated reading time for this post is 4 minutes

In the security terms, Authentication is the operation to verify the user and Authorization is the rights that have been assigned to the user. Securing SQL Server is not only involving to these two terms, instead is also about providing right privileges to every single user and manage them properly without having any security conflict.

Windows vs. SQL Server Authentication

SQL Server can accept two different authentication mode such as Windows and SQL Server, Windows authentication is applicable and suitable to the environment which having Active Directory service. By windows authentication, application or user does not need to send the username and password through the network to SQL Server, therefor the security token from the Active Directory service will transferred through network. SQL Server authentication mode is more suitable for untrusted domains, it means the environment without Active Directory service, and user or application needs to send the username and password to the SQL Server through network.

Login Management

Login is an account defined in the server level and it is the first step towards giving permission in SQL Server. Administrators need to consider below options.

  • Enforce Password Policy
  • Enforce Password Expiration
  • User Must Change Password at Next Login
  • Default Language

SQL Server logins can be locked due to the password policy. Every login can change their own password without providing the old password and only “sysadmin” or “securityadmin” server role members do not need to provide the old password; otherwise old password must be provided during changing the password. SQL Server stores the login password as Hash value and it is not reversible encryption method. Administrators can use PWDCOMPARE undocumented function to check what user think is the current password.

Brute-Force Attack Solution

Brute-Force is the way to crack the password by trying every possible character combination. To avoid this kind of attack, administrators enable the following options in SQL Server.

  • Password Check Policy
  • Password Expiration Policy
  • Password Must Be Changed at First Login

Limiting SA Account Privileges

SA is a historical and well-known account in SQL Server, then usually attackers use this account to achieve their goals. The best practice is to disable and rename SA account to reduce security risk. The following code renames and disables SA account.

ALTER Login SA Disable;
ALTER Login SA WITH Name = SQL_SystemAdministrator;

Giving Granular Server Privileges

Fixed server roles are just wrappers around the server privileges and the permission provided by fixed server role is beyond what user needs. The best practice is to give the granular server privileges to prevent security vulnerability.  The following code returns all the server level permissions.

SELECT * FROM Sys.Server_Permissions;

Administrator has ability to create server role in SQL Server and assign the granular server level permission to it. Below code creates new server level role.

CREATE Server ROLE SemiAdministrators;
ALTER ROLE SemiAdministrators ADD Member [HamidJFard];

Below table is the list of fixed server roles: –

Fixed Server RoleDescription
Bulk AdminCan runs BULK INSERT commands
DBCreatorCan create, alter, drop and restore any database
DiskAdminCan manage files on the disk but needs permissions to alter a database to add or change files or filegroups inside. DiskAdmin alone is not very useful.
ProcessAdminCan view and kill sessions, a regular login can view its own session.
SecurityAdminCan create and change a login. But cannot create a server role or give permissions to a login which he does not himself possess.
ServerAdminCan change the instance properties and stop/restart it.
SetupAdminCan create and manage linked-servers.
SysAdminHas a full administration privilege on the instance and all the attached databases. No permission can be denied to a sysadmin member. Issuing an explicit deny on any securable to it will have no effect.

Prevent User to See Metadata

Metadata is the most critical data in SQL Server and it must be invisible from non-administrators. To prevent user to see metadata, administrators can grant the login on “View Any Database” permission. Master and TempDB databases will remain visible to all logins. Below code makes all databases invisible for every user except their own databases.

DENY VIEW Any DATABASE TO PUBLIC;

Contained Database

Contained database is not depend on any external definition and it can be moved to any other server easily without require any extra configuration. There are several levels of containment such as Non-Contained, Partially Contained and Fully Contained which are explained in below: –

  • Non-Contained: The database depends on server, and a user can be seen across databases.
  • Partially-Contained: The user is defined in the database, but it still can access resources outside of the database.
  • Fully-Contained: The database is independent and the user cannot access any resource out of the database.

Contained database solves mismatch SID and collation of temporary tables with single # issues. SQL Server is the only version that can support partially contained database and the “Contained Database Authentication” option must be enabled during database restoration or attachment.

The following code shows all contained users: –

SELECT [Name],[Default_Schema_Name],[Type_Desc]
FROM Sys.Database_Principals WHERE Authentication_Type = 2;

The following code changes the database containment type: –

EXECUTE SP_CONFIGURE 'Contained Database Authentication', 1;
RECONFIGURE WITH Override;
ALTER DATABASE CURRENT SET Containment = PARTIAL;

Resolving the SID Mismatch Issue

Mismatch SID issue happens when the database is moved to other server and the related logins are not available. To resolve this issue, administrators take three different actions such as: –

SELECT DP.name, DP.sid FROM Sys.database_principals DP
LEFT Join Sys.server_principals SP ON SP.sid = DP.sid
WHERE DP.principal_id > 4 and DP.type_desc = 'SQL_User' and DP.sid IS NULL;
And the following code resolves the mismatch SID issue.
EXECUTE SP_CHANGE_USERS_LOGIN          @ACTION = 'Auto_Fix', @LoginName = 'HamidJFard', @Password = 'Pa$$w0rd';

I hope this blog post was informative for you, if so; please share with others. I will explain techniques about the protecting data in SQL Server in the next blog post.

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz