SQL Server Security Basics | The Principle of Least Privilege

We need to walk a fine line when granting privileges to a user to perform certain task. Principle of Least Privilege says that a user be granted only those privileges, which are required to perform their task – nothing more, nothing less. This principle is also known as the principle of minimal privilege or the principle of least authority.

We should always follow the principle of least privilege when granting permissions to database users. A database user, whose task is to extract reports, should be granted read permissions to the schema. Image if the same user has write permissions to the database, potentially database can be compromised; sales data can be fudged to make it look great before the management if a user has extra privileges.

There is no direct way or a charter, which lists set of permissions vis-à-vis functional task. It varies from organization to organization. In order to come up with a security model, start by assigning least level of privileges (that may seem appropriate to you) to perform the intended task and then test. As a quick tip try to remove one privilege at a time and check the impact on the task under consideration. This way you will be able to formulate a security model.

I came across an incident recently where a team received a database backup (was in Oracle though not SQL Server) from a customer to fix an issue and a user account that was used to restore the backup actually overwrite password profiles that were set-up internally. It was discovered very late when some of the application users reported passwords being expired after one week of creation.

Security is very important for an organization to sustain for long, and there is no alternative but to conscientiously follow principle of least privilege.




SQL Server Security Basics | What is Authorization?

Think of this:

  • Whether a stranger is authorized to enter your house without your permission?
  • Whether you authorize your friend to take your car whenever he wants to go on a drive?
  • Whether a co-employee is authorized to access your confidential information stored with Human Resource department?

You might be getting a hang of it… Authorization is all about “What can a person (or identity in digital world) do?”; have they so called “access rights/privileges” to the desired “resources”.

That being said, Authorization takes a form of access policies that an organization sets forth for the resources being used. These access policies are created and/or controlled by an authority (usually a senior employee or department head).  These policies are formulated based on “principle of least privilege” – which says that a user/identity should only have minimum set of privileges to get their work done.

In SQL Server, Authorization is enforced with Permissions, and we have a freedom to club common permissions into Roles. These permissions are hierarchical in nature and exist both at database and server level.

I will talk more about Authorizations, Permission Hierarchy, and principle of least privilege in upcoming blogs. So stay tuned!

SQL Server Security Basics | What is Authentication?

By definition Authentication means the process of verifying the identity of a user or process. If a user wants to talk to the database, SQL Server asks “Who you are?”, and authenticates you. There are three types of authentication modes available:

  • Windows Authentication
  • SQL Server Authentication
  • Azure Active Directory

Windows Authentication

  • This is the default authentication mode and the more secure as compared to SQL Server Authentication.
  • Microsoft BOL recommends to use Windows Authentication over SQL Server Authentication.
  • This mode is available both on SQL Server running on-premises and Azure Virtual Machine.
  • It uses Kerberos security protocol.
  • Connection made under this mode is also called “Trusted Connection” as SQL Server trusts Windows credentials.
  • Has additional password policy, such as strong password validation, support for account lock and password expiration.

SQL Server Authentication

  • Logins here are validated which are created and managed by SQL Server.
  • Unlike Windows Authentication, user should provide credentials every time while connecting to SQL Server.
  • There are few (optional) password policies also available.
  • This mode can be used where there is a requirement to support mixed operating system for applications and users cannot be validated using Windows domain.
  • Can be useful with web-applications where users have the provision to create their own identities.
  • It does not use Kerberos security protocol, and there is also a risk for applications that connect automatically with the SQL Server may save the password in file in clear text.

Azure Active Directory

  • This authentication mechanism validates an identity based on Azure Active Directory (Azure AD).
  • It supports token-based authentication, ADFS (domain federation) and/or built-in vanilla authentication without domain synchronization.
  • It can also support mechanism of password rotation in a single place.
  • Allows management of identities centrally (Central ID), which helps in simplifying user and permission management.

I am going to write a series of blogs on security basics, this one is first one in the row. Stay tuned.


Choosing Authentication Mode

Azure AD Authentication

SQL Server (vNext) on Linux – CTP 1.0


Last month November 16th, 2016, Microsoft announced its first Community Technology Preview of next release of SQL Server, called SQL Server vNext. This release will run not only on Windows but also no Linux, Docker, or macOS (via Docker).

Here is the download link for SQL Server vNext CTP 1.0

Sneak peak: New features and enhancements

Database Engine

  • Addition of new compatibility level 140
  • Improvement in incremental statistics update threshold (available through new database COMPATIBILITY_LEVEL 140)
  • Addition of new DMVs:  sys.dm_exec_query_statistics_xml (return live query plan and execution statistics for the running batch), sys.dm_os_host_info (provide operating system information for both Windows and Linux)
  • Many performance and language enhancements to In-Memory tables:
    • Support for more than 8 indexes
    • Support for sp_spaceused, sp_rename, CASE statement, TOP (N) WITH TIES
  • Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).
  • New function: STRING_AGG()
  • Database roles are created with R Services for managing permissions associated with packages
  • Addition for new Japanese Collation

R Services

  • Microsoft R Server and SQL Server R Services provide a variety of new features to enhance integration of R with SQL Server and the Microsoft BI stack

Integration Services (SSIS)

  • Support Scale Out of SSIS – easier to run SSIS package on multiple machines
  • Support for Microsoft Dynamics Online Resources – connection to Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online thru OData source and connection manager

Useful Links


How to use insert query to store images

I have been usually asked the question on how to insert  
images using insert statements. Here is the way to do that…

Let’s create a table to store images having two columns one using IMAGE data type and other using VARBINARY data type. My intention here is to demonstrate that image can be stored in either of the two types in same manner.

CREATE TABLE image_tbl
     img_varbinary VARBINARY(max),
     img_image     IMAGE

We shall now use OPENROWSET with BULK mode to read the image data. BULK mode allows us to interpret and read the image data in a binary stream. This is where we take the advantage and insert the data as a binary stream into our table.

In the first example we shall look at queries inserting in column with VARBINARY data type using JPEG and PNG image.

INSERT INTO image_tbl(img_varbinary)
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA;
INSERT INTO image_tbl(img_varbinary)
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;

Next, we shall look at queries inserting data in column with IMAGE data type using same JPEG and PNG images.

INSERT INTO image_tbl(img_image)
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA;


INSERT INTO image_tbl(img_image)
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;

Here is how the output looks like:


Length of replicated LOB data exceeds configured maximum

We had set-up peer-to-peer replication on three geographically distinct nodes with around 800 articles being published with data type of lot many columns as varchar(max), image and text data. Once the replication was up and running, users reported this error “com.microsoft.sqlserver.jdbc.SQLServerException: Length of LOB data (70199) to be replicated exceeds configured maximum 65536”.

Reason for this issue: Default SQL Server configuration supports “Max text replication size” of 65536 bytes (2 power 16 bytes) for text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data columns. We get this exception when in a single insert or update statement data size for any of the replicated text column violates this limit.

Solution: There are two ways to check and rectify this issue. Once using T-SQL and another using SSMS

Using T-SQL
Check the server configuration for current max text replication size

EXEC sp_configure 'show advanced options', 1 ; 
EXEC sp_configure 'max text repl size'; 

-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536

Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.

-- For SQL Server 2005
EXEC sp_configure 'show advanced options', 1 ; 
EXEC sp_configure 'max text repl size', 2147483647 ; 

-- For SQL Server 2008 (and 2008 R2)
EXEC sp_configure 'show advanced options', 1 ; 
EXEC sp_configure 'max text repl size', -1 ; 

-1 indicates that there is no limit other than imposed by the data type itself. Furthermore, max data replication option applies to transactional replication and CDC (Change Data Capture) and is ignored for snapshot replication and merge replication.

Using SSMS

  1. Open SSMS and connect to object explorer
  2. Right-click on the server name (in explorer) and choose properties
  3. Select “Advanced” options on the properties page.
  4. Max text replication Size is available under “Miscellaneous” header.
  5. Change the value from 65536 to -1 or 2147483647 (depending on the version on SQL Server) and press OK.