Practice using separate account and database for SQL RedGate Build

ntkhanh_tma

Hi all,

I tried to find the solution for my scenario but I couldn't get close to an answer so I hope someone can point me to an article on how to.

Scenario:
- I have Azure DevOps setup with TFS integration.
- I have a fresh database with no data and schema to be used for SQL RedGate Build 
- I have changesets managed by SQL Source Control in TFS
- The fresh database is accessible by a new login/user and it will have db_owner permission on that database only. The login can't be used in other database.

Issue:
- Everytime I run the pipeline with RedGate SQL Change Automation: Build, the steps will fail at some point because the process would drop users then add it back but in vain. Like this:

##[debug]Invoke-DatabaseBuild : Couldn't connect to the database specified by the TemporaryDatabase parameter: The server principal "sqlAutomation" is not able to access the database "SchemaTrunk" under the current security context.

##[debug]Cannot open database "SchemaTrunk" requested by the login. The login failed.

##[debug]Login failed for user 'sqlAutomation'.

Question:
- If the login/user setup for Change Automation is wrong, which is the correct way to do it from the start?
- Could I config the build to not drop users and just focus on the schema parts?
- How could I fix the issue above and proceed with the build, test, and deploy?

I tried to use LocalDB but it doesn't support TRIM, EXTERNAL, and other stuff so I guess it is not a choice for me.

Thanks,
Khanh Nguyen

DanC

Hi @ntkhanh_tma

To address this issue and to avoid that user being dropped at this stage you should use a filter to exclude that user and then specify the path to that filter on the build definitions in TFS:


Could you try configuring this and letting me know if you come across any other issues and I'll provide further support!

DanC

ntkhanh_tma said:

Hi @DanC,

I have setup the filter as 
And tried:
1. Not include the Filter path as the tool says it will pick the default filter file in source control (I committed it there). 
Result: Not work, same issue.

2. Include the Filter path ($/Project.SQL/Filter.scpf)
Result: Error: The specified path either doesn't exist or can't be accessed by the current Windows account: 'D:\a\17\s\$\CurrentProject\Project.SQL\Filter.scpf'

Am I missing any configuration here?

I was hoping that we can exclude the Users entirely from the process in both comparing and putting into source control as normally you don't want to replicate users from database to database, especially between dev and production.

Edit: nevermind, it was my fault that the error happened. Dan's solution works perfectly. But I wonder why the tool doesn't pick up the filter while it is right there in the source control folder as default.

Regards.

Sometimes this can be for a few reasons, such as a slight misconfiguration with the paths for accessing the resources or using a different agent pool. 

I'm pleased it's working for you now and if you do get further issues, don't hesitate to reach out to us directly on support!

ntkhanh_tma

Hi @DanC

Sounds like a solution. I have already set the filter to only include users with name = "1" in order to not commit users to the changeset but I have never thought of doing the other way around!

I will try and update back later.

Thanks!

ntkhanh_tma

Hi @DanC,

I have setup the filter as 
And tried:
1. Not include the Filter path as the tool says it will pick the default filter file in source control (I committed it there). 
Result: Not work, same issue.

2. Include the Filter path ($/Project.SQL/Filter.scpf)
Result: Error: The specified path either doesn't exist or can't be accessed by the current Windows account: 'D:\a\17\s\$\CurrentProject\Project.SQL\Filter.scpf'

Am I missing any configuration here?

I was hoping that we can exclude the Users entirely from the process in both comparing and putting into source control as normally you don't want to replicate users from database to database, especially between dev and production.

Edit: nevermind, it was my fault that the error happened. Dan's solution works perfectly. But I wonder why the tool doesn't pick up the filter while it is right there in the source control folder as default.

Regards.