1

in a database that is part of an availability group (alwayson), in the current primary server I create a certificate and sign some stored procedures using it.

all is working fine so far.

use mydatabase
go


-- check if certificate exist, create otherwise
IF NOT EXISTS(SELECT 1 FROM SYS.CERTIFICATES WHERE NAME = 'dyntsqlcert')
BEGIN
    CREATE CERTIFICATE dyntsqlcert   
    FROM FILE = 'C:\Certificates\dyntsqlcert.cer'   
    WITH PRIVATE KEY (FILE = 'C:\Certificates\dyntsqlKey.pvk',
    ENCRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa', 
    DECRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa');  
 END


--check if a user for the certificate exist, create otherwise
IF NOT EXISTS(SELECT * FROM SYS.database_principals where name ='dyntsql')
BEGIN
    CREATE USER [dyntsql] FOR CERTIFICATE [dyntsqlcert];
END

-- Granting permissions to the tables
        GRANT SELECT ON [app].[applicant] TO [dyntsql];
        GRANT SELECT ON [usr].[user] TO [dyntsql];
        GRANT SELECT ON [usr].[userCountry] TO [dyntsql];
        GRANT SELECT ON [app].[country] TO [dyntsql];
        GRANT SELECT ON [app].[application] TO [dyntsql];
        GRANT SELECT ON [upl].[applicationDocument] TO [dyntsql];
        GRANT SELECT ON [upl].[document] TO [dyntsql];
        GRANT SELECT ON [upl].[applicationDocumentImages] TO [dyntsql];
        GRANT SELECT ON [upl].[ref_documentType] TO [dyntsql];
        GRANT SELECT ON [app].[program] TO [dyntsql];
        GRANT SELECT ON [app].[ref_applicationStatus] TO [dyntsql];
        GRANT SELECT ON [app].[ref_CPIStatus] TO [dyntsql];
        GRANT SELECT ON [app].[applicationItemGroupChange] TO [dyntsql];
        GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
        --GRANT SELECT ON [APIA_Repl_Sub].[repl].[flightChangesHistory] TO [dyntsql];
        GRANT SELECT ON [app].[applicationstatechange] TO [dyntsql];
        GRANT SELECT ON [app].[applicationPlacementInfo] TO [dyntsql];
        GRANT SELECT ON [app].[applicationPreDepartureQuestions] TO [dyntsql];
        GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
        GRANT SELECT ON [app].[ref_visaStatus] TO [dyntsql];
        GRANT SELECT ON [agy].[agency] TO [dyntsql];

--Adding Signature to SPS'
IF NOT EXISTS
(
    SELECT 1
    FROM sys.crypt_properties cp
    JOIN sys.objects o ON cp.major_id = o.object_id
    LEFT JOIN sys.certificates cer ON cp.thumbprint = cer.thumbprint AND
    cp.crypt_type IN ('SPVC', 'CPVC')
    LEFT JOIN sys.asymmetric_keys ak ON cp.thumbprint = ak.thumbprint AND
    cp.crypt_type IN ('SPVA', 'CPVA')
    WHERE o.name = 'usp_sel_workqueue_applicationsWithNewMedia'
)
BEGIN 
    ADD SIGNATURE TO [app].usp_sel_workqueue_applicationsWithNewMedia
    BY  CERTIFICATE [dyntsqlcert] WITH PASSWORD = 'oH8F*%G9pnyqfa';
END

question is, in the event of a failover, when this current server becomes the secondary, alongside its c:\ drive, would the certificate still be valid?

I should be testing this instead of asking but I don't have a good testing environment in hand.

1

Since the certificate and permissions are all included at the database level, there should be no reason to re-create the certificate. Always On will take care of transferring the data to the secondary, it should just be there if it's synchronized or synchronizing but matching log block numbers in the DMVs.

If you wanted the certificate to "be" in the "C:\Certificates" folder and constantly use it (not clear as to why from the questions) then it'll need to be on each replica. I'd also say that this would be bad as the keys are on the same server as the databases, which sort of defeats the purpose.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.