Mitigating Double Hop in SQL Server!

The estimated reading time for this post is 1 minutes

ssis agent fail 3

In most advance and complex databases, developers and DBAs faces double hop security issue, which usually DBAs granting the permissions to resolve the issue but actually they are putting database into high security risk.

To mitigate double hop issue, SQL Server uses certificate to sign the executable modules to make sure it has not altered by other user. It can be used to authenticate objects between servers or databases and replace ownership chaining as well (I will explain Replace Ownership Chaining in the next post.).

SQL Server can sign modules such as Stored Procedures, Functions, DML Triggers and Assemblies.

Scenario: There is an user called “John”, John has permission to execute a stored procedure called “USP_GetSales” but John does not and strictly should not have “Select” permission on the “dbo.Sales” table, due to confidentiality.

Resolution: By signing USP_GetSales stored procedure, John can execute the module without having “Select” permission on “dbo.Sales” table. To sign the stored procedure:

  1. Create Certificate
  2. Sign Stored Procedure by Certificate
  3. Create User from the Certificate

For mentioned scenario, below scripts can resolve the double hop issue within the database.

Best practice is to remove the private key from the certificate or asymmetric key to prevent other users sign modified module.

Author: Hamid Jabarpour Fard

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:

Leave a Comment

Notify of