[OT] Creating logins/users/roles/permissions in Visual Studio sqlproj projects
Hello I'm fairly new to Visual Studio sqlproj projects and was wondering what the normal practice is for managing database security scripts (user logins, users, roles, permissions). I have seen some other projects here where the previous developers had the create login SQL code for example in post-deployment scripts, however, I see this being a problem since different environments (different publish profiles in the project) would require different logins and different security altogether. On top of that I'm not even sure how the passwords would be managed in this case as they would need to be hardcoded in the scripts. In the past I'm sure I've only ever seen the security purely managed by the DBAs in external scripts (or manually) and not in the Visual Studio project itself. Any thoughts or recommendations would be much appreciated. Regards Tom
Hi Tom, For roles and permissions, they should just go directly in the DB projects. Nothing special about them. Users/logins are different. Ideally, you’d have AD or AAD based groups that are added to the roles, and again they can go right in the project. If you’re looking after the DB or deploying it, you really shouldn’t be getting involved in who is in those groups, and there’s nothing more for you to do. If you have to use SQL logins/users (and you really should now be trying to avoid those), you can put them in the projects but you don’t want passwords there. It then depends how you are deploying the projects. If it’s something like Azure Pipelines or GitHub Actions, then you should put the credentials that must be there into Azure Key Vault or similar, and retrieve them from there during deployment. If you are in an environment like Azure, then you should be reassessing why you need the SQL logins. If it’s an app connecting to a DB, in many cases, a user-assigned managed identity would be a better option. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile SQL Down Under | Web: https://sqldownunder.com<https://sqldownunder.com/> | About Greg: https://about.me/greg.low From: Tom P via ozdotnet <ozdotnet@ozdotnet.com> Sent: Thursday, 8 December 2022 9:58 AM To: ozDotNet <ozdotnet@ozdotnet.com> Cc: Tom P <tompbiz93@gmail.com> Subject: [OT] Creating logins/users/roles/permissions in Visual Studio sqlproj projects Hello I'm fairly new to Visual Studio sqlproj projects and was wondering what the normal practice is for managing database security scripts (user logins, users, roles, permissions). I have seen some other projects here where the previous developers had the create login SQL code for example in post-deployment scripts, however, I see this being a problem since different environments (different publish profiles in the project) would require different logins and different security altogether. On top of that I'm not even sure how the passwords would be managed in this case as they would need to be hardcoded in the scripts. In the past I'm sure I've only ever seen the security purely managed by the DBAs in external scripts (or manually) and not in the Visual Studio project itself. Any thoughts or recommendations would be much appreciated. Regards Tom
Thanks for the advice, Greg. Unfortunately, the setup is "legacy" using SQL logins and AD auth is not an option here. Deployment strategies are currently being investigated but it looks like they may be going with the old TFS pipelines. I guess the actual logins and assignment to roles would need to be managed separately to the DB projects it sounds like..... Regards Tom On Thu, 8 Dec 2022 at 10:11, Dr Greg Low <greg@sqldownunder.com> wrote:
Hi Tom,
For roles and permissions, they should just go directly in the DB projects. Nothing special about them.
Users/logins are different.
Ideally, you’d have AD or AAD based groups that are added to the roles, and again they can go right in the project. If you’re looking after the DB or deploying it, you really shouldn’t be getting involved in who is in those groups, and there’s nothing more for you to do.
If you have to use SQL logins/users (and you really should now be trying to avoid those), you can put them in the projects but you don’t want passwords there. It then depends how you are deploying the projects. If it’s something like Azure Pipelines or GitHub Actions, then you should put the credentials that must be there into Azure Key Vault or similar, and retrieve them from there during deployment.
If you are in an environment like Azure, then you should be reassessing why you need the SQL logins. If it’s an app connecting to a DB, in many cases, a user-assigned managed identity would be a better option.
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile
SQL Down Under | Web: https://sqldownunder.com | About Greg: https://about.me/greg.low
*From:* Tom P via ozdotnet <ozdotnet@ozdotnet.com> *Sent:* Thursday, 8 December 2022 9:58 AM *To:* ozDotNet <ozdotnet@ozdotnet.com> *Cc:* Tom P <tompbiz93@gmail.com> *Subject:* [OT] Creating logins/users/roles/permissions in Visual Studio sqlproj projects
Hello
I'm fairly new to Visual Studio sqlproj projects and was wondering what the normal practice is for managing database security scripts (user logins, users, roles, permissions).
I have seen some other projects here where the previous developers had the create login SQL code for example in post-deployment scripts, however, I see this being a problem since different environments (different publish profiles in the project) would require different logins and different security altogether. On top of that I'm not even sure how the passwords would be managed in this case as they would need to be hardcoded in the scripts.
In the past I'm sure I've only ever seen the security purely managed by the DBAs in external scripts (or manually) and not in the Visual Studio project itself.
Any thoughts or recommendations would be much appreciated.
Regards
Tom
That’s a pity. But you can, as you mention, have post deploy steps that put them in place. Ideally though, they’d still come from something like Key Vault instead of being in the scripts or in files somewhere. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile SQL Down Under | Web: https://sqldownunder.com<https://sqldownunder.com/> | About Greg: https://about.me/greg.low From: Tom P <tompbiz93@gmail.com> Sent: Thursday, 8 December 2022 10:53 AM To: Dr Greg Low <greg@sqldownunder.com> Cc: ozDotNet <ozdotnet@ozdotnet.com> Subject: Re: [OT] Creating logins/users/roles/permissions in Visual Studio sqlproj projects Thanks for the advice, Greg. Unfortunately, the setup is "legacy" using SQL logins and AD auth is not an option here. Deployment strategies are currently being investigated but it looks like they may be going with the old TFS pipelines. I guess the actual logins and assignment to roles would need to be managed separately to the DB projects it sounds like..... Regards Tom On Thu, 8 Dec 2022 at 10:11, Dr Greg Low <greg@sqldownunder.com<mailto:greg@sqldownunder.com>> wrote: Hi Tom, For roles and permissions, they should just go directly in the DB projects. Nothing special about them. Users/logins are different. Ideally, you’d have AD or AAD based groups that are added to the roles, and again they can go right in the project. If you’re looking after the DB or deploying it, you really shouldn’t be getting involved in who is in those groups, and there’s nothing more for you to do. If you have to use SQL logins/users (and you really should now be trying to avoid those), you can put them in the projects but you don’t want passwords there. It then depends how you are deploying the projects. If it’s something like Azure Pipelines or GitHub Actions, then you should put the credentials that must be there into Azure Key Vault or similar, and retrieve them from there during deployment. If you are in an environment like Azure, then you should be reassessing why you need the SQL logins. If it’s an app connecting to a DB, in many cases, a user-assigned managed identity would be a better option. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile SQL Down Under | Web: https://sqldownunder.com<https://sqldownunder.com/> | About Greg: https://about.me/greg.low From: Tom P via ozdotnet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>> Sent: Thursday, 8 December 2022 9:58 AM To: ozDotNet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>> Cc: Tom P <tompbiz93@gmail.com<mailto:tompbiz93@gmail.com>> Subject: [OT] Creating logins/users/roles/permissions in Visual Studio sqlproj projects Hello I'm fairly new to Visual Studio sqlproj projects and was wondering what the normal practice is for managing database security scripts (user logins, users, roles, permissions). I have seen some other projects here where the previous developers had the create login SQL code for example in post-deployment scripts, however, I see this being a problem since different environments (different publish profiles in the project) would require different logins and different security altogether. On top of that I'm not even sure how the passwords would be managed in this case as they would need to be hardcoded in the scripts. In the past I'm sure I've only ever seen the security purely managed by the DBAs in external scripts (or manually) and not in the Visual Studio project itself. Any thoughts or recommendations would be much appreciated. Regards Tom
participants (2)
-
Dr Greg Low -
Tom P