Mitigating Double Hop in SQL Server!

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

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