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.
Best Answer
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.