September 17, 2018 at 5:55 am
Morning Gang,
I have an application written in .NET - for once we have full source code for it, and can compile/recompile as necessary.
At the moment there is no proper security embedded in the application. It's using a SQL Login (shared amongst all users).
I want to, if possible, limit those who can connect to the DB by certificate and AD Group. The AD Group is simple enough. But I am not sure if its possible, or where to look or how to google protecting access to certificate holders only? Is it possible?
I don't even know if I am asking the right question!
Ultimately, the application is an in-house designed Password Manager (it stores more than just passwords mind, but lets say it needs to store and encrypt sensitive stuff, sometimes passwords, sometimes files)
What would the best way of achieving this be?
I understand this is leaning more towards a development question rather than strictly SQL Server, but I thought I'd, just in case.
Cheers
Alex
September 17, 2018 at 3:50 pm
alex.sqldba - Monday, September 17, 2018 5:55 AMMorning Gang,I have an application written in .NET - for once we have full source code for it, and can compile/recompile as necessary.
At the moment there is no proper security embedded in the application. It's using a SQL Login (shared amongst all users).
I want to, if possible, limit those who can connect to the DB by certificate and AD Group. The AD Group is simple enough. But I am not sure if its possible, or where to look or how to google protecting access to certificate holders only? Is it possible?
I don't even know if I am asking the right question!
Ultimately, the application is an in-house designed Password Manager (it stores more than just passwords mind, but lets say it needs to store and encrypt sensitive stuff, sometimes passwords, sometimes files)
What would the best way of achieving this be?
I understand this is leaning more towards a development question rather than strictly SQL Server, but I thought I'd, just in case.
Cheers
Alex
Not with a login created from a certificate - those can't connect to SQL Server. They are used for signing stored procedures, assemblies.
You could still look at using this but I'm guessing it's not a flip of a switch. It's changing how you have the security setup by not allowing any direct access to the tables, do everything through stored procedures and you can sign those stored procedures if needed.
If this app was done with direct access to tables for everything then taking the time to get rid of that, access through stored procedures only is what would protect the database.
Sue
September 17, 2018 at 4:12 pm
Is this an internally used application or one that is internet facing where anyone can access it? From your question it seems like an internal access only application. You may want to see if it can be changed to require AD logins and then use AD Groups to control appropriate access based on the roles defined by the groups.
September 24, 2018 at 1:20 am
Ah yes it is internally used and is added to a domain, and is in fact currently controlled by an AD Group. But the brass here quite specifically want it [also] to be secured by a Certificate in the Certificate Store. Hence I am at a bit of a loss.
September 24, 2018 at 8:36 am
alex.sqldba - Monday, September 24, 2018 1:20 AMAh yes it is internally used and is added to a domain, and is in fact currently controlled by an AD Group. But the brass here quite specifically want it [also] to be secured by a Certificate in the Certificate Store. Hence I am at a bit of a loss.
You could print this article, highlight the third NOTE in the article and give it to them:
Authentication in SQL Server
Note
SQL Server provides logins created from certificates or asymmetric keys that are used only for code signing. They cannot be used to connect to SQL Server.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply