February 9, 2021 at 4:51 am
Hi Experts,
We are currently using SQL authentication in all our applications and the security team has raised the concern that the Release team or whoever manages application servers can get the credentials and access DB servers. Now DB team has to give an option to overcome this, need expert advice on using windows authentication for all applications.
The Pros I can see is windows authentication help prevent the application server team from accessing SQL Servers.
Cons: Application change is required.
Am I missing something here?
TIA
February 9, 2021 at 11:57 am
OK, I'm a little confused by how the question is phrased. It sounds like you & the security team are hesitant to use Windows Authentication? Don't be. Use it. It's much, much better than SQL Authentication.
Now, as to who has access to do what, here, there are some core concepts that you should be following, and yeah, some of them are absolutely going to require an appropriate architecture, which could mean changes.
Follow the least privilege principal. This means, a given login has enough privileges to do what it needs. Only what it needs. No, "just in case" or "we can use it for more than one thing" or "it's just easier to make everything 'sa'". You specify just enough access for a given login. Does this mean you may have more than one login in the system? Yes. Absolutely.
If this means changing the applications because they've all been logging in under a single common account that has access to everything, well, change them. You can't both make development silly simple and appropriately secure a system. Security takes work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 10, 2021 at 7:26 am
OK, I'm a little confused by how the question is phrased. It sounds like you & the security team are hesitant to use Windows Authentication? Don't be. Use it. It's much, much better than SQL Authentication.
Now, as to who has access to do what, here, there are some core concepts that you should be following, and yeah, some of them are absolutely going to require an appropriate architecture, which could mean changes.
Follow the least privilege principal. This means, a given login has enough privileges to do what it needs. Only what it needs. No, "just in case" or "we can use it for more than one thing" or "it's just easier to make everything 'sa'". You specify just enough access for a given login. Does this mean you may have more than one login in the system? Yes. Absolutely.
If this means changing the applications because they've all been logging in under a single common account that has access to everything, well, change them. You can't both make development silly simple and appropriately secure a system. Security takes work.
Thanks Grant.
Currently, all our applications are using SQL authentication. We are concerned about the impact it is going to cause from application point of view, more than that will it help make our system secure from the above-mentioned issue, I think Yes but need advise from experts like you.
All our application account(currently SQL authentication) have read, write & execute permissions. Will it be possible for anyone use windows credentials to access SQL Server from application servers ? As per my understanding someone have to login with those credentials on application server and then access SQL Server through any tool or they need to build an application which should run inside the applciation server just like our deployed applications. Correct me if I am wrong.
TIA
February 10, 2021 at 12:13 pm
At the most fundamental level, access to data & structures within a database, there are zero differences between a SQL login and a Windows login. You will map logins to users within the databases. Preferably, those users are managed through roles. Roles have access to do stuff, whatever that stuff is, execute a stored procedure, read from a table, modify a view... All the things.
There's nothing inherent in a windows auth that means you MUST give it more, or less, access to the system. The security setup is merely different. One can easily argue, better. MUCH better. In fact, pretty much, the industry standard for how you ought to be doing things. However, inside the server, inside the database, the behaviors are identical.
You will have to change your applications. You can't simply switch off a SQL login and the app that depends on it will know that it now has to use a trusted connection based on the service account it's running under. At the very least, your connection strings have to be rewritten.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2021 at 11:31 am
At the most fundamental level, access to data & structures within a database, there are zero differences between a SQL login and a Windows login. You will map logins to users within the databases. Preferably, those users are managed through roles. Roles have access to do stuff, whatever that stuff is, execute a stored procedure, read from a table, modify a view... All the things.
There's nothing inherent in a windows auth that means you MUST give it more, or less, access to the system. The security setup is merely different. One can easily argue, better. MUCH better. In fact, pretty much, the industry standard for how you ought to be doing things. However, inside the server, inside the database, the behaviors are identical.
You will have to change your applications. You can't simply switch off a SQL login and the app that depends on it will know that it now has to use a trusted connection based on the service account it's running under. At the very least, your connection strings have to be rewritten.
Thanks Grant. Can you help me understand what are all the options people can access SQL Server if they have windows account and password and the SQL Server connectivity is only from application server?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply