SCA support for stored procedure signing
NigelH
Posts: 4 Bronze 1
I am having to add a certificate and a certificate mapped user to an existing SCA-deployed database and then add signatures to a couple of stored procedures.
What is the recommended way of handling this in the SCA project?
What is the recommended way of handling this in the SCA project?
Tagged:
Answers
May I ask for further details, like are you experiencing any issues with performing this currently?
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?
The issue was just that SCA doesn't include the creation of the certificate (presumably because that would necessitate adding the password into the migration script) so, unless that step was completed manually before deployment, the subsequent creation of the certificate mapped user, user permissions, etc. would all fail. Of course, the shadow database would also need the certificate or refreshing against the target database would also fail.
I get the reasons for omitting the certificate and eventually went with the slightly clunky workaround of testing for the existence of the certificate in the migration script and creating a dummy one if it didn't. That took care of the shadow database and it didn't matter that the password was available in plain text unless the certificate wasn't created ahead of deployment onto a real database.
However, it all turned out to be a molehill, not a mountain, as the problem I was trying to address (the error "Only active directory users can impersonate other active directory users" when running stored procedures containing dynamic SQL and the statement "WITH EXECUTE AS OWNER" to allow a non-AD database login to run them) was not a permissions problem at all, but was a database owner issue. I eventually realised that one environment worked ok, whereas two others threw this error. They contained databases created by restoring bacpac files from the working environment and, because the owner is a SQL account, not AD, the SIDs didn't match up, leaving the owner property invalid. I just ran a "ALTER AUTHORIZATION ON DATABASE::" statement and the certificate, etc. was unnecessary.
Thanks anyway.
Regards,
Nigel
I'm pleased to hear you was able to identify the core issue and find a solution!
If you need any further help don't hesitate to reach out
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?