Security vs. Monitoring – Solution by using module signing

In this post, I provide my solution for the Security vs. Monitoring puzzle. I will use module signing.

First, let’s think a little bit proactive and I assume that there will be more special request in the future when we need to create some further code for monitoring, so I create a separate database called ‘Monitoring’. The ‘monitoring’ user will have rights only in this database, and just as much as it really needed.

After that I create a stored procedure called ‘usp_monitor’ with EXECUTE AS clause and in this user defined stored procedure the sp_monitor is called. I grant execute permission to the ‘monitoring’ user.

Nothing special so far. As I mentioned before, I use module signing to provide all the necessary permissions. For this I create a certificate in the ‘monitoring’ database then I sign the usp_monitoring with this certificate. After that I backup this certificate and then delete the primary key part so no-one can sign again the stored procedure in case of any modification without the primary key.

In the end, I restore the public part of the certificate to the master database, and I create a login from it, and grant authenticate server privilege to this login.

We are done. Only one thing left. Move the certificate (especially the private key part) to secure place. If you have any question feel free to contact me, leave a comment or drop me a mail /robertATsqlapprenticeDOTnet/.

Further on I will provide some very useful links/articles about this topic.

This entry was posted in Security. Bookmark the permalink.