locked
Is there a straightforward way to tell if the cryptographic keys on two databases are the same or different? RRS feed

  • Question

  • I have run these queries al most did not return any rows (except as noted).  Based on this, can someone tell me how I can determine if the keys (server keys? I'm not sure what I have because it was set up before I started) are the same or different?  Or would I need to supply more information for someone to answer?  If so, what kind of information.  Clearly my expertise isn't in this form of SQL Server security.

    select * from sys.certificates
    select * from sys.asymmetric_keys
    select * from sys.symmetric_keys
    select * from sys.key_encryptions
    select * from sys.master_Key_passwords 
    select * from sys.cryptographic_providers
    select * from sys.crypt_properties
    select * from sys.credentials  --returns rows
    select * from sys.login_token  --returns rows
    select * from sys.openkeys
    select * from sys.securable_classes --returns rows
    select * from sys.server_audits
    select * from sys.system_components_surface_area_configuration  --returns rows
    select * from sys.server_file_audits
    select * from sys.user_token  --returns one row

    Thanks for all help.

    Wednesday, September 18, 2013 1:39 PM

Answers

  • Well. Those system tables are empty because there are no objects then. And therefore nothing to compare.

    If you do not know what exactly you need, you could query all databases and check if any of them has such keys in them.. and still it may not be what that person wants. The only thing that I can think of as "Server key" is the Service master key, which you can query from master only:

    select name, key_guid from master.sys.symmetric_keys 

    There you have the GUID instead of the thumbprint for uniqueness.

    If that's not what you need, you need to get a more concrete request from whoever is asking you. Somebody gotta know what he really want's, right? Other than that we can only guess. He could also be asking for the windows product key after all, you see?

    After all: "There is nothing like a straight forward and precise question." ;-)


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, September 18, 2013 4:46 PM

All replies

  • Both asymmetric keys and symmetric keys as well as certificates have a (globally) unique thumbprint, which you can compare:

    select name, thumbprint from sys.certificates
    select name, thumbprint from sys.asymmetric_keys select name, key_thumbprint from sys.symmetric_keys

    Everything else in your list is a completely different type of object and usually has just a primary key. Some of them are actually just "detail records" of those "major objects" like the keys themselves.

    You should just look those system views up in Books Online to understand them better. This will most certainly answer all your questions.

    Catalog Views



    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, September 18, 2013 3:32 PM
  • Thanks for the reply Andreas, I appreciate it.  But what if the three queries you supplied above all return zero rows in my databases yet there are keys (somewhere).  This is why I am asking.  I am hoping someone can help me get an answer so that I can provide the information to the person who asked me in a timely manner while I learn more about security as I can while putting out fires in other cares.  

    Is there perhaps a windows server key that's at least partially external to SQL Server so that's why I'm not getting any rows back from the three queries?

    Thanks again.

    Wednesday, September 18, 2013 4:30 PM
  • Well. Those system tables are empty because there are no objects then. And therefore nothing to compare.

    If you do not know what exactly you need, you could query all databases and check if any of them has such keys in them.. and still it may not be what that person wants. The only thing that I can think of as "Server key" is the Service master key, which you can query from master only:

    select name, key_guid from master.sys.symmetric_keys 

    There you have the GUID instead of the thumbprint for uniqueness.

    If that's not what you need, you need to get a more concrete request from whoever is asking you. Somebody gotta know what he really want's, right? Other than that we can only guess. He could also be asking for the windows product key after all, you see?

    After all: "There is nothing like a straight forward and precise question." ;-)


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, September 18, 2013 4:46 PM
  • The query you gave me: 

    select name, key_guid from master.sys.symmetric_keys 

    gave me results.  I didn't realize that these were stored in the master DB.  I will provide these results for all the servers and see what he says.

    Thanks very much.  Very useful information.  I understand about eliciting clear information with details from the person asking but it's difficult when that person is the boss' boss who is quite formal, i.e. barely talks to me, strict chain-of-command sort of deal.  He's not mad he's just formal and I'm new.  Fortunately I never said I was a security expert (other than understanding database privileges and such).  In other words, when I send him responses, he writes back and says: That still doesn't answer the question.  Great.

    I'll post again if I get any more information / can figure something out. That way anyone looking for similar information will benefit from what you've provided (definitely) and what I might provide in the future (possibly).

    Update: I ran the query for symmetric keys in the master database for some production DB servers and some non-production DB servers. All the ##MS_DatabaseMasterKey## and ##MS_ServiceMasterKey## values are different from every other.  If I know that the production servers allow database backups to be recovered to a new database on other production servers but I know also that production databases cannot be restored to non-production DB servers, can you help me figure out why?  I know I'm being vague.  I haven't seen the error in a month so I don't know the error # or verbiage.  I was hoping that the values returned from the query (above) would be the same for the prod boxes and different from the non-prod boxes but they are all different from each other so the explanation lies elsewhere.  If this is not a clear enough question to answer, that's fine.  I really appreciate what you've already told me so far.



    • Edited by ThorNotFromNorway Wednesday, September 18, 2013 5:22 PM add a new question
    Wednesday, September 18, 2013 5:02 PM
  • I totally understand. I hope your boss also understands, that even an expert needs to know accurately what is being asked for. Otherwise the latter can throw dozens of things onto his table, which in the end produces more work for both.

    Of course some context would help a lot, like “for what” he needs that key. What’s the plan/project.. then someone with some knowledge might get the idea what would be the “natural need”. If you know more, let us know, otherwise you need to get more information yourself..

    Good luck in the conversation ;-)


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, September 18, 2013 5:10 PM