Category Archives: Security

Common Criteria Compliance and LCK_M_SCH_M

–By Lori Brown

If you have ever wondered what happens when you mistakenly enable Common Criteria compliance in SQL, well wonder no more!!

Blog_20170427_1

We have a client who has no idea how or when Common Criteria was enabled on their production system. All they know is that performance has been slowly degrading. After collecting performance data, we found that there were high LCK_M_SCH_M waits which is a schema modification lock that prevents access to a table while a DDL operation occurs. We also found blocked process records where a LOGIN_STATS table in the master database was waiting a lot. This table is used to hold login statistics. When there are a lot of logins and outs there can be contention in this table.

When you enable Common Criteria compliance, something called Residual Information Protection (RIP) is enabled. RIP is an additional security measure for memory and it makes it so that in memory a specific bit pattern must be present before memory can be reallocated(overwritten) to a new resource or login. So with lots of logins and outs, there is a performance hit in memory because overwriting the memory allocation has to be done.

Keep in mind if you enable Common Criteria compliance, you can run into slowdowns from locking and memory. Make sure that your server is able to handle this well and that applications are designed to minimize the impact of high logins\logouts.

To disable Common Criteria compliance you can use sp_configure or the GUI.

sp_configure ‘common criteria compliance enabled’, 0

GO

RECONFIGURE

GO

However, it is not really disabled until you reboot the server (it actually says to reboot the server in MSDN). Restarting the instance will not work for this configuration change. I believe that this is because of the Residual Information Protection that secures memory. It stands to reason that without a reboot that flushes memory that RIP is still doing its work and causing performance issues.

Check out the documentation from MSDN on Common Criteria compliance here:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/common-criteria-compliance-enabled-server-configuration-option

The biggest takeaway from this is to make sure to change your SQL configuration with good reason. Always know the side effects that can show up when you set any of the advanced configuration options.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Login Failures Bloating SQL Server Log

–By Ginger Keys

After migrating a client’s SQL instances to VMs from physical servers, I noticed the following week that the SQL Server Log files had tons of failed login attempts from what looked like an application. These attempts were happening several times per second, and had totally bloated the SQL Server logs.

Blog_20170316_1

This is not desirable because 1) obviously something should not be attempting and failing to connect that much, and 2) it makes it super cumbersome to monitor more critical messages in the SQL logs when so many login errors are bloating the log. Too many failed login attempts could mean that the application has not been set with the correct permissions to access the databases needed, or it could be an indication of a hacking attempt.

I ran the script below to determine how many failed login attempts had occurred in the last 7 days, and the count came back with over 3 million failed attempts!

— Create a temporary table

CREATE TABLE #FailedLogins

(Logdate DATETIME,

Process VARCHAR(20),

Text VARCHAR(4000))

 

— Insert data from SQL Server Log into temp table

INSERT INTO #FailedLogins

EXEC xp_readerrorlog 1, 1, N’Login Failed’, N’LoginName’,‘20170309’,‘20170316’

 

— For more info about xp_readerrorlog click here https://sqlandme.com/2012/01/25/sql-server-reading-errorlog-with-xp_readerrorlog/

  –Count number of occurrences

SELECT COUNT(Text) AS LoginFailures, Text

FROM #FailedLogins

GROUP BY TEXT

 

–Drop temp table

DROP TABLE #FailedLogins

 

As it turns out it was a legacy system moved from the old environment that needed to be adjusted to connect to the new environment. I didn’t exactly get to put on my superwoman cape and solve the problem, but at least I was able to identify the issue and direct it to the appropriate IT staff to correct.

As part of your everyday duties as a DBA it is prudent to monitor your SQL Server Logs to catch hacking attempts or malicious activity with your instance, and also to look for errors that could be occurring. By default, SQL contains 7 error log files (one current, and six archives). Error logs contain critical information about your SQL server. A new log is created each time the SQL service starts, and the oldest archived log gets deleted. For security reasons it is best practice to keep a large number of error logs, depending on the space you have available. Someone with access to your SQL server could execute sp_cycle_errorlog and regenerate the logs at a rate that could ‘delete’ their malicious activity or cover up the fact that they have gained access to your system. If you have highly sensitive data or stringent auditing requirements you can increase the number of retained error logs up to 99, just be sure to monitor your disk space.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Synchronize Logins on AlwaysOn Replicas

–By Lori Brown   @SQLSupahStah

At one of my clients who has an AlwaysOn failover cluster, I noticed as I was checking up on things on the secondary replica that there were several fewer Windows and SQL logins on the secondary than on the primary. I really did not want to tediously compare the logins on each so instead came up with a script that can be run periodically to script out any logins that need to be synched up between the replicas. I actually found most of the script that I needed on the SQLSoldier’s blog (http://www.sqlsoldier.com/wp/) in a stored procedure that was intended to synch logins between mirrored partners. Since AlwaysOn is Mirroring on steroids I thought that it would work and it did. I did put my own touches on the proc by turning it into a standalone script that uses OPENROWSET to query the AG primary replica and also changed it from automatically executing the statements for missing logins to simply printing them into a script that can be applied as needed.

And now….here is what you came here for….

— Original logic from http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

— Sync Logins to AlwaysOn Replicas

—     Inputs: @PartnerServer – Target Instance (InstName or Machine\NamedInst or Instname,port)

—     Output: All Statements to create logins with SID and Password for both Windows and SQL logins

—             Will also add logins to server roles

—     Person executing this must be sysadmin

—     Ad Hoc Distributed Queries must be enabled for this to work without setting up a linked server

/*

— Turn on Ad Hoc Distributed Queries so we don’t have to set up a linked server

sp_configure ‘show advanced options’, 1

GO

reconfigure with override

go

sp_configure ‘Ad Hoc Distributed Queries’, 1

GO

reconfigure with override

go

*/

 

Use master;

Go

Declare @MaxID int,

@CurrID int,

@PartnerServer sysname,

@SQL nvarchar(max),

@LoginName sysname,

@IsDisabled int,

@Type char(1),

@SID varbinary(85),

@SIDString nvarchar(100),

@PasswordHash varbinary(256),

@PasswordHashString nvarchar(300),

@RoleName sysname,

@Machine sysname,

@PermState nvarchar(60),

@PermName sysname,

@Class tinyint,

@MajorID int,

@ErrNumber int,

@ErrSeverity int,

@ErrState int,

@ErrProcedure sysname,

@ErrLine int,

@ErrMsg nvarchar(2048)

 

SET @PartnerServer = ‘InstName’

 

Declare @Logins Table (LoginID int identity(1, 1) not null primary key,

[Name] sysname not null,

[SID] varbinary(85) not null,

IsDisabled int not null,

[Type] char(1) not null,

PasswordHash varbinary(256) null)

 

Declare @Roles Table (RoleID int identity(1, 1) not null primary key,

RoleName sysname not null,

LoginName sysname not null)

 

Declare @Perms Table (PermID int identity(1, 1) not null primary key,

LoginName sysname not null,

PermState nvarchar(60) not null,

PermName sysname not null,

Class tinyint not null,

ClassDesc nvarchar(60) not null,

MajorID int not null,

SubLoginName sysname null,

SubEndPointName sysname null)

 

Set NoCount On;

 

If CharIndex(‘\’, @PartnerServer) > 0 — Check for Named Instance

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘\’, @PartnerServer) – 1);

End

Else If CharIndex(‘,’, @PartnerServer) > 0 — Check for Instance with port in connection string

Begin

Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);

End

Else

Begin

Set @Machine = @PartnerServer;

End

 

— Get all Windows logins from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name, P.sid, P.is_disabled, P.type, L.password_hash

             From master.sys.server_principals P

             Left Join master.sys.sql_logins L On L.principal_id = P.principal_id

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)

Exec sp_executesql @SQL;

 

— Get all roles from principal server

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select RoleP.name as RoleName, LoginP.name as LoginName

             From master.sys.server_role_members RM

             Inner Join master.sys.server_principals RoleP

             On RoleP.principal_id = RM.role_principal_id

             Inner Join master.sys.server_principals LoginP

             On LoginP.principal_id = RM.member_principal_id

             Where LoginP.type In (””U””, ””G””, ””S””)

             And LoginP.name <> ””sa””

             And LoginP.name Not Like ””##%””

             And RoleP.type = ””R””

             And CharIndex(””’ + @Machine + ‘\””, LoginP.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Roles (RoleName, LoginName)

Exec sp_executesql @SQL;

 

— Get all explicitly granted permissions

— using OPENROWSET and Windows Authentication

Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name Collate database_default,

                    SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,

                    SubP.name Collate database_default,

                    SubEP.name Collate database_default

             From master.sys.server_principals P

             Inner Join master.sys.server_permissions SP

             On SP.grantee_principal_id = P.principal_id

             Left Join master.sys.server_principals SubP

             On SubP.principal_id = SP.major_id And SP.class = 101

             Left Join master.sys.endpoints SubEP

             On SubEP.endpoint_id = SP.major_id And SP.class = 105

             Where P.type In (””U””, ””G””, ””S””)

             And P.name <> ””sa””

             And P.name Not Like ””##%””

             And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’

 

–print @SQL

 

Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)

Exec sp_executesql @SQL;

 

Select @MaxID = Max(LoginID), @CurrID = 1

From @Logins;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = Name,

@IsDisabled = IsDisabled,

@Type = [Type],

@SID = [SID],

@PasswordHash = PasswordHash

From @Logins

Where LoginID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals

Where name = @LoginName)

Begin

Set @SQL = ‘Create Login ‘ + quotename(@LoginName)

If @Type In (‘U’, ‘G’)

Begin

Set @SQL = @SQL + ‘ From Windows;’

End

Else

Begin

Set @PasswordHashString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@PasswordHash”))’, ‘nvarchar(300)’);

 

Set @SQL = @SQL + ‘ With Password = ‘ + @PasswordHashString + ‘ HASHED, ‘;

 

Set @SIDString = ‘0x’ +

Cast( As XML).value(‘xs:hexBinary(sql:variable(“@SID”))’, ‘nvarchar(100)’);

 

Set @SQL = @SQL + ‘SID = ‘ + @SIDString + ‘;’;

End

 

Print @SQL;

 

If @IsDisabled = 1

Begin

Set @SQL = ‘Alter Login ‘ + quotename(@LoginName) + ‘ Disable;’

Print @SQL;

End

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(RoleID), @CurrID = 1

From @Roles;

 

While @CurrID <= @MaxID

Begin

Select @LoginName = LoginName,

@RoleName = RoleName

From @Roles

Where RoleID = @CurrID;

 

If Not Exists (Select 1 From sys.server_role_members RM

Inner Join sys.server_principals RoleP

On RoleP.principal_id = RM.role_principal_id

Inner Join sys.server_principals LoginP

On LoginP.principal_id = RM.member_principal_id

Where LoginP.type In (‘U’, ‘G’, ‘S’)

And RoleP.type = ‘R’

And RoleP.name = @RoleName

And LoginP.name = @LoginName)

Begin

Print ‘Exec sp_addsrvrolemember @rolename = ”’ + @RoleName + ”’, @loginame = ”’ + @LoginName + ”’;’;

End

Set @CurrID = @CurrID + 1;

End

 

Select @MaxID = Max(PermID), @CurrID = 1

From @Perms;

 

While @CurrID <= @MaxID

Begin

Select @PermState = PermState,

@PermName = PermName,

@Class = Class,

@LoginName = LoginName,

@MajorID = MajorID,

@SQL = PermState + space(1) + PermName + SPACE(1) +

Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)

When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)

Else End +

‘ To ‘ + QUOTENAME(LoginName) + ‘;’

From @Perms

Where PermID = @CurrID;

 

If Not Exists (Select 1 From sys.server_principals P

Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id

Where SP.state_desc = @PermState

And SP.permission_name = @PermName

And SP.class = @Class

And P.name = @LoginName

And SP.major_id = @MajorID)

Begin

Print @SQL;

End

Set @CurrID = @CurrID + 1;

End

 

Set NoCount Off;

GO

 

/*

— Turn off Ad Hoc Distributed Queries

sp_configure ‘Ad Hoc Distributed Queries’, 0

GO

reconfigure with override

go

*/

On my test AG replicas, you can see that there are two users that are not present on the secondary. I need to add them so that if a failover occurs then there will not be any login failures.

blog_20170105_1

Using the script above on SQLSRVTST2, I get the following output and can use that to create my users with their correct permissions. I had to set the @PartnerServer variable to SQLSRVTST1 in the script and enable Ad Hoc Distributed Queries.

blog_20170105_2

……

blog_20170105_3

blog_20170105_4

The nice thing is that the password and SID’s are also scripted for you. There is no need to install sp_hexadecimal and sp_help_revlogin in master. If you have AG replicas with a lot of logins, this can be a real timesaver for you. Hope you enjoy and if you make any improvements, please share them with us!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Script to De-Orphan SQL Logins

–By Lori Brown   @SQLSupahStah

I am working with a client who is migrating from SQL 2012 to SQL 2016. It just so happens that while they have just a few databases to move, they also have a ton of SQL logins that needed to be migrated. This is not an issue since I know to use sp_help_revlogin to script logins and passwords into a script that can be applied to the new SQL 2016. However, inevitably, there are SQL logins that have been deleted from SQL but not from the database or while testing on SQL 2016 the databases are refreshed and now logins are orphaned. I needed to have a script that would look at each database and give me a script that could be used to create or de-orphan these logins.

I figured that with a little Google-fu I would find someone who had already created a way to do this. And, while I did find a solution, I decided to give it my own twist. My solution is to have a script that I can run when needed that will output the statements that I need to create or de-orphan logins from all databases in an instance. I combined things from these two blogs:

http://www.sqlshack.com/creating-a-stored-procedure-to-fix-orphaned-database-users/

https://www.codeproject.com/articles/489617/create-a-cursor-using-dynamic-sql-query

Here is my code and a quick look at the output:

USE master

SET NOCOUNT ON

DECLARE @usercount INT

DECLARE @sqlstr1 NVARCHAR(1000)

DECLARE @sqlstr2 NVARCHAR(500)

DECLARE @dbname VARCHAR(128)

DECLARE @username SYSNAME

DECLARE @OrphUsers AS CURSOR

 

DECLARE db CURSOR FOR

SELECT name FROM sys.databases

WHERE name NOT IN (‘master’,‘tempdb’, ‘msdb’, ‘model’)

AND is_read_only = 0 AND state = 0

 

OPEN db

FETCH NEXT FROM db INTO @dbname

WHILE @@FETCH_STATUS=0

BEGIN

 

SET @sqlstr1 = ‘SET @orphanedusers = CURSOR

       FOR SELECT a.NAME

       FROM [‘+@dbname+‘].sys.database_principals a

       LEFT OUTER JOIN sys.server_principals b

             ON a.sid = b.sid

       WHERE (b.sid IS NULL)

            AND (a.principal_id > 4)

             AND (a.type_desc = ”SQL_USER”)

 

       OPEN @orphanedusers’

 

EXEC sys.sp_executesql @sqlstr1, N’@orphanedusers cursor output’, @OrphUsers OUTPUT

 

FETCH NEXT FROM @OrphUsers INTO @username

WHILE @@FETCH_STATUS=0

BEGIN

 

SELECT @usercount = COUNT(*) FROM sys.server_principals WHERE name = @username

 

IF @usercount = 0

BEGIN

PRINT ‘–User ‘+@username+‘ does not exist and must be created. Please use a strong password.’

SET @sqlstr2 = ‘USE [master] CREATE LOGIN ‘ + @username + ‘ WITH PASSWORD = ”ABC123”’

END

             ELSE

             BEGIN

                    PRINT ‘–User ‘+@username+‘ is an orphan.’

SET @sqlstr2 = ‘USE [‘+@dbname+‘] ALTER USER ‘ + @username + ‘ WITH LOGIN = ‘ + @username

END

 

PRINT (@sqlstr2)

FETCH NEXT FROM @OrphUsers INTO @username

END

CLOSE @OrphUsers

DEALLOCATE @OrphUsers

 

FETCH NEXT FROM db INTO @dbname

END

CLOSE db

DEALLOCATE db

 

SET NOCOUNT OFF

blog_20161229_1

The output labels what needs to be done to make sure the users have access to their databases. If the user does not exist in SQL at all then a CREATE LOGIN statement is generated. If the user is simply orphaned an ALTER LOGIN statement is generated.

The only thing I did not do yet (mostly because my needs were centered around SQL logins) is to include Windows domain accounts in my logic. As soon as I add that to my script, I will repost. Or, if someone out there adds it, please send an update.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

SQL Server Security – Controlling Access

— by Ginger Keys

Installing SQL following best practices is an important first step in securing your SQL server. The next step in securing your SQL server is to decide who can access the SQL instance, what databases and other objects they need access to, and what kind of permission to give them to those objects. So what is involved in securing access to your SQL server? We will go over the basic components, and discuss best practices to help keep your server secure.

blog_20161020_1

There are three main areas of security within your SQL server instance.

  • Principals – these are the persons or entities needing to access your SQL server.
  • Securables – these are the databases, objects, and other resources in your SQL server instance.
  • Permissions – these determine which resources a principal can access and what actions they are allowed to perform.

There are also some other important areas to consider in controlling access to your SQL server:

  • Schemas and Ownership

 

  • SQL server service accounts
  • Administrative accounts

 

Principals

Principals are persons, applications, and entities that can be authenticated to access the SQL server resources. In SQL server, principals include logins, users, and roles.

LOGINS – Principals require a login in order to connect to the SQL server. Logins are at the server level only, and provide for the entry point, or the initial connection to the server. Logins are validated against the master database and the connection is to the instance, not the databases or other components.   SQL supports two methods for authenticating logins: Windows and SQL authentication. Mixed mode authentication allows for the use of either Windows or SQL logins.

Windows logins can include individual users, groups, domains accounts, or local accounts. Group accounts are granted login access to all logins that are members of the group. SQL relies on Windows to authenticate these accounts.

SQL logins are specific to the instance, and are stored in SQL, with the username and hash of the password in the master database. SQL uses internal authentication to validate login attempts. This type of login may be necessary for users not associated with a Windows domain.

Best Practices for Logins:

  • Use Windows authentication whenever possible
  • create Windows groups in Active Directory set with appropriate access/permissions to the SQL server, then add individual users to the appropriate groups
  • Do not use SQL authentication for logins if possible –when SQL Server logins are used, SQL Server login names and passwords are passed across the network, which makes them less secure
  • Audit SQL Server failed login attempts to monitor for possible hacking activity

USERS – Once logged in to a SQL instance, a user account is necessary in order to connect to a database and its components. Users are created within a database, and mapped back to the server login.

User accounts that are not mapped to a login account are known as orphaned users. An exception is contained database users; they do not need to map to a login.

Guest user – this account is a built-in account in SQL server, and is disabled in new databases by default. The guest user allows a login to access databases without being mapped to a specific database user, and it inherits the ‘public’ database role with its permissions.

dbo user – this account has implied permissions to perform all activities in the database. Any principals belonging to the sysadmin fixed server role are mapped to the dbo user account automatically. The dbo user is in every database and is a member of the db_owner database role.

Best Practices for Users:

  • Disable the guest user in every user database (not system DBs)
  • If you must use the guest account, grant it minimum permissions

ROLES – Roles exists at both the server and database level. Permissions can be assigned to a role which makes it more efficient to manage principals’ access to securables. Permissions are given to roles, then logins and users can be added to (or removed from) roles.

Server roles – server level roles can be fixed or user defined. Members of server roles have permissions to sever-level securables, and cannot be changed or revoked. Logins can be assigned to fixed server roles without having a user account in a database.     

For complete list and description of server roles click here https://msdn.microsoft.com/en-us/library/ms188659.aspx

Database roles – These roles have a pre-defined set of permissions. Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles.

For complete list and description of database roles click here https://msdn.microsoft.com/library/ms189121.aspx

Public role – The public role is contained in every database including system databases. It cannot be dropped and you can’t add or remove users from it. Permissions granted to the public role are inherited by all users because they belong to the public role by default.

Best Practices for Roles:

  • Be very cautious when adding users to fixed server roles:
    • Do not add principals to the sysadmin role unless they are highly trusted.
    • Membership in the securityadmin role allows principals to control server permissions, and should be treated with the same caution as the sysadmin role.
    • Be very cautious in adding members to the bulkadmin role. This role can insert data from any local file into a table, which could put your data at risk. For more information click here https://msdn.microsoft.com/library/ms188659.aspx

 

 

  • Grant public role only the permissions you want all users to have, and revoke unnecessary privileges.

Securables

SQL Server securables are the resources that can be accessed by a principal. SQL server resources operate within a hierarchy, with the server at the top of the hierarchy. Below the server instance lies the databases, and below the databases are a collection of objects (schemas, tables, views, etc.). Access to securables is controlled by granting or denying permissions to principals, or by adding or removing principals (logins and users) to roles which have access. All securables have an owner. The owner of a securable has absolute control over the securable and cannot be denied any privilege. Server level securables are owned by server principals (logins), and database level securables are owned by database principals (users).

blog_20161215_1

Permissions:

Permissions determine the type of access granted on a securable to a specific principal and what tasks a principal can perform on securables. The TSQL permission statements are

GRANT

REVOKE

DENY

Granting permission to a principal removes any DENY or REVOKE on that securable. A permission given at a higher scope in the hierarchy that contains that securable will take precedence over the lower level permission statement. Database level permissions only apply to that specific database.

Owners of securables can grant permissions on the objects they own, and any principal with CONTROL permissions can grant permissions on that securable as well.

Best Practices for Permissions:

  • Always use the principal of least privilege, which limits users by granting the minimum permissions necessary to accomplish a task. For more information click here https://msdn.microsoft.com/library/ms191291.aspx
  • Document any elevated user permission and request managerial approval.
  • When developing an application use a least-privileged user account (LUA), which may be more difficult – but will eliminate the temptation to grant elevated privileges as a quick fix when an end user cannot perform certain tasks that the administrative developer could. Granting elevated permissions to users in order to acquire lost functionality after the development phase can leave your application vulnerable to attack.
  • Grant permissions to roles rather that to users. It is easier to add and remove users from roles, than to manage separate permission sets for individual users.
  • Don’t grant individual users access to SQL Server, instead create groups for specific servers with specific permissions, and add individual users to the appropriate groups.

Ownership & Schemas 

Owners of objects have irrevocable permissions to administer those objects. The owner of a securable has absolute control over the securable and cannot be denied any privilege. You cannot remove privileges from an object owner, and you cannot drop users from a database if they own objects in it. Server level securables are owned by server principals (logins), and database level securables are owned by database principals (users).

A schema is a named container for similar database objects, and can be owned by any principal. You can assign security rules for a schema which will then be inherited by all objects in that schema.

Objects can be moved between schemas, and schema ownership can be transferred between principals.

Best Practices for Schemas:

  • In your TSQL, refer to the objects using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.). Example: databasename.schemaname.tablename.
  • Use synonyms to obfuscate the schema ownership of objects.
  • Use schemas to protect the base database object from being altered or removed from the database by users without sysadmin permissions.
  • Use schemas to combine related, logical entities to reduce administration overhead.
  • For more info – https://technet.microsoft.com/en-us/library/dd283095(v=sql.100).aspx

SQL Server Service Accounts 

Depending on what features and components you decide to install, SQL Server has several services that are used to manage authentication of SQL Server with Windows. These services need user accounts associated with them to start and run the services. The service accounts should have exactly the privileges that it needs to do its job and no more. For a list of all available SQL services click here https://msdn.microsoft.com/en-us/library/ms143504.aspx .

Best Practices for Service Accounts:

  • Assign service accounts low-level permissions using the principle of least privilege.
  • Don’t use built-in Windows system accounts (e.g., Network Service, Local System) for SQL Server service accounts – the built-in system accounts inherit certain elevated rights in Active Directory that aren’t required by SQL Server.
  • Use a Managed Service Account (MSA) if resources external to the SQL Server will be used.
  • When MSA are not possible, use a specific low-privilege non-user domain account.
  • Use separate accounts for different SQL Server services. The service accounts should not only be different from one another, they should not be used by any other service on the same server.
  • SQL Server Agent account is the only service account that requires membership in the systems admin server role.
  • Do not grant additional permissions to the SQL Server service account or the service groups – SQL Server installation program will grant them the local rights that they need during the installation.
  • Always use SQL Server Configuration Manager to change service accounts or passwords.
  • Don’t add service accounts to the Local Administrator group
  • Use a service account for applications, and restrict the applications access only to data required – not every object in the database

Administrative Accounts

sa Account – SQL Server creates this server level login during installation by default. The sa account has full administrative rights in your SQL Server instance, and is well known and often targeted by hackers. The sa login maps to the sysadmin fixed server role, which has irrevocable administrative privileges on the whole server.

BUILTIN\Administrators – All members of the Windows BUILTIN\Administrators group are members of the sysadmin role by default. This built in group is no longer included in SQL Server 2008 and later.

Best Practices for Administrative Accounts

  • Disable or rename the sa account – make sure another account exists with admin privileges before doing this.
  • If you must use the sa account, assign a strong password and enforce password policies
  • Do not use the sa account for day-to-day administration or logging on to the server remotely
  • Do not allow applications to use the sa account to connect to SQL
  • Remove the BUILTIN/Administrators group from the SQL Server Logins if it’s present in your instance.
  • Restrict users with system admin privileges, using server roles instead.
  • Restrict members of local administrator group. Limit the administrative access only to those who really require it.
  • Assign every administrator a named login, and don’t allow shared logins so that you can identify the users behind each and every database change.
  • Document all users with administrative rights, and any elevated user permissions

Conclusion

Controlling access to your SQL Server should be well thought out ahead of time. Every organization needs to have an information security policy in place, and the role of the DBA is to enforce these policies and protect the SQL Server instances along with the data in them. The suggestions provided above give a basic guideline for controlling who has access to your SQL Server. Utilizing windows groups, database roles, applying the principle of least privilege, and implementing the other suggestions above can be instrumental in keeping your SQL environments safe.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!