Security in SQL Server – Part 3

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.

[codesyntax lang=”tsql”]

Alter Login SA Disable;
Alter Login SA With Name = SQL_SystemAdministrator;

[/codesyntax]

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.

[codesyntax lang=”tsql”]

Select * From Sys.Server_Permissions;

[/codesyntax]

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.

[codesyntax lang=”tsql”]

Create Server Role SemiAdministrators;

Alter Role SemiAdministrators Add Member [HamidJFard];

[/codesyntax]

Below table is the list of fixed server roles: –

[table id=36 /]

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.

[codesyntax lang=”tsql”]

Deny View Any Database To Public;

[/codesyntax]

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

[codesyntax lang=”tsql”]

Select [Name],[Default_Schema_Name],[Type_Desc]

From Sys.Database_Principals Where Authentication_Type = 2;

[/codesyntax]

The following code changes the database containment type: –

[codesyntax lang=”tsql”]

Execute SP_Configure ‘Contained Database Authentication’, 1;

Reconfigure With Override;

Alter Database Current Set Containment = Partial;

[/codesyntax]

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

[codesyntax lang=”tsql”]

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’;

[/codesyntax]

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.

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan