What are the challenges you face when working across database platforms? Take the survey
Options

SCA support for stored procedure signing

NigelHNigelH Posts: 4 Bronze 1
edited March 9, 2023 12:05PM in SQL Change Automation
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?

Answers

  • Options
    Hi @NigelH

    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?

  • Options
    NigelHNigelH Posts: 4 Bronze 1
    Hi Dan,

    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
  • Options
    Hi @NigelH

    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?

Sign In or Register to comment.