locked
Using certificates across Servers RRS feed

  • Question

  • Hi,

    we've a lot of stored procedures that access resources in different databases and also across different instances.

    In order to ensure that granting a login/user execute on the stored procedure is sufficient, without having to grant permissions to undelying tables, I'm considering doing one of the following.

    1 - switch on DB chaining for all databases. The database owners are already the same for each database

    2 - Sign all procedures with a certificate, the login associated with the certificate would have sufficient acces to all databases.

    This doesn't however get around the problem that we have many stored procedures that access resources on a different SQL instance.

    I would like to create a certificate that I can sign a stored procedure with and that would be effective across both my SQL instances, so if I signed a stored procedure on instance A and granted a user permissions on that stored procedure, the user would need no additional permissions if the stored procedure access resources (tables etc) on server B, because the same certificate exists there and is associated with a login with sufficient permissions.

    I'd appreciate any suggestions or advice, I've looked at various articles/bol/blogs etc but can't find an answer to this quetion.

    I'm going to try out various approaches today, but would really appreciate some guidance if possible.

    thanks

    Sean


    Sean

    Monday, February 25, 2013 11:41 AM

Answers

  • So it could make sense to sign procedure to get cross-database calls going. But for cross-server calls it's taking you nowhere. When you access a linked server, that is just like you have logged in interactively on that server.

    You can of course sign procedures on all servers, but whether you use the same certificate on all servers, or a separate certificate for each server does not really matter.

    I also like to point out that while using lots of databases and servers may make your look your application modular and flexible, linked servers often means hassle, with permissions, authentication, performance and error and transaction handling. There may be good reason to split up things over different server, but in such case, consider an asynchronous solution based on Service Broker or maybe even SSIS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sean2000 Tuesday, February 26, 2013 11:36 AM
    Monday, February 25, 2013 10:28 PM

All replies

  • Hi,

    I thought that Cross Database Ownership would handle the permissions issue with sp's across sql instances.

    Here is an site that has good info addressing similarities in your post.

    http://www.sommarskog.se/grantperm.html

    Note: sometimes it takes a couple of refreshes for it to load.


    A SQL Server MVP or MSFT Eng should be replying soon as well. Hope this helps. Frank Garcia *** Please select "Vote As Helpful" if the information provided was helpful to you. If an answer to your issue solved the problem then please mark it as "Propose As Answer" located at the bottom. Thank you. ***

    Monday, February 25, 2013 4:04 PM
  • So it could make sense to sign procedure to get cross-database calls going. But for cross-server calls it's taking you nowhere. When you access a linked server, that is just like you have logged in interactively on that server.

    You can of course sign procedures on all servers, but whether you use the same certificate on all servers, or a separate certificate for each server does not really matter.

    I also like to point out that while using lots of databases and servers may make your look your application modular and flexible, linked servers often means hassle, with permissions, authentication, performance and error and transaction handling. There may be good reason to split up things over different server, but in such case, consider an asynchronous solution based on Service Broker or maybe even SSIS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sean2000 Tuesday, February 26, 2013 11:36 AM
    Monday, February 25, 2013 10:28 PM
  • Hi,

    thanks for the reply. I've long had this link on my favourites list and refer to it regularly. It doesn't however answer the question of Cross server calls.


    Sean

    Tuesday, February 26, 2013 11:08 AM
  • thanks for the reply. I've long had this link on my favourites list and refer to it regularly. It doesn't however answer the question of Cross server calls.

    I hope that my reply answers your question. I will admit that I have not considered the situation, but I am not sure that I will modify the article to cover the scenario. After all, there is not much to say but "it doesn't work".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 26, 2013 11:22 AM
  • Thank you for your reply and also your excellent "Giving Permissions through stored procedures"  web page which I've referred to many times.

    So it seems that no matter whether I use database chaining or certifictaes, whenever a stored procedure accesses a linked server it reverts back to the context of the original caller.

    It is good to have this answer as I won't waste anymore time going down a dead end road. As the majority of cross database calls are to a common database I am looking at replication to make this database available on the other servers. I will look at the use of Service Broker as well.

    Again thanks for the help.


    Sean

    Tuesday, February 26, 2013 11:36 AM
  • So it seems that no matter whether I use database chaining or certifictaes, whenever a stored procedure accesses a linked server it reverts back to the context of the original caller.

    I don't like the word "revert" here, since it is a new connection. If there is a self-mapping to the linked server, the new connection will use the same credentials, but there could also be a login-mapping set up to log in as a specific SQL login.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 26, 2013 10:39 PM