question

SQL_DBA avatar image
SQL_DBA asked

Loginless Users

We have a custom application that updates a database. I wish to ensure that all access to the database must be done via the custom application. With that in mind I propose using a loginless user to prevent user from directly accessing the database using their Windows id. However, how do I prevent them from loging onto sql via ssms and then doing an 'execute as user 'loginless user name'?
users
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think that I'm misunderstanding what I need to do to achieve this. The custom application needs the end users to have permissions to the sql server and the application database so as to be able to 'EXECUTE AS USER LOGINLESSUSERNAME' - this all works ok and I'm happy with that. However I can't figure out how to stop the users running the 'execute as...' from SSMS
0 Likes 0 ·
Why does it need to EXECUTE AS? Can't you create a role with a specific set of permissions and assign the users to that role? EXECUTE AS is a masking mechanism for a sub-set of your security settings, not a fundamental piece of the puzzle.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
As long as someone doesn't have permissions to the server, they can't connect to it. If they have permissions to the server, but not to the database, they can't connect to the database. If they have permissions to connect to both, then you just have to restrict what they can do on the database by using DENY on privileges. If, however, you're in the situation where you've set everyone up as 'sa' there's nothing you can do.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
I think I understand the situation (or at least I hope that I do). @Grant Fritchey is absolutely correct here, but I would like to add my 2 cents. If your custom application uses SSPI so every application user connects to the database as respective windows principal then it is just plain evil and should be avoided if at all possible. I understand that it is somewhat convenient because - there is no need to maintain passwords - SQL Server takes care of denying connection to someone who is no longer an active AD user - Placing members of a certain AD group into the role is handy for granular permissions The problem with SSPI is that the connection pooling is suffering greatly. It is much easier for SQL Server to manage the pool if every application user has the **same DNA**. If this is the case in your scenario then you can solve the problem you have by simply denying impersonate permission to the users who login to SSMS as windows principals while still preserving the ability of the SQL Server login used by the application to impersonate your loginness account which has appropriate permissions to do whatever your procedures used by the application are doing. For example, suppose that all developers connecting to the SSMS using windows authentication are members of a certain role, and while you need the account used to connect the application to use **execute as**, you don't want the developers to run any queries as your loginness user. deny impersonate on user::your_loginness_user to some_devs_role cascade; go You don't necessarily need a **cascade** option, it all depends on whether explicit grant permissions were used for any specific users. In case if your application uses SSPI, there is no way to get what you need as @Grant Fritchey has already pointed out in his answer. Oleg
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice. I think you nailed it.
0 Likes 0 ·
Only add things related to the connection pooling. This is true in case of web application. In this case if the web service is using the same SQL Login or AD account of the service than it will benefit from connection pooling. In case of Thick client, than event each user connects under his login, he will benefit from connection pooling because he will always connect using his account. The pooling is done on the client side. The same connectio npool is used for each connection using the same connection string. So it is important to keep the same connection string across the whole application whenever possible.
0 Likes 0 ·
The benefit of a Loginless User is in audit trails. We can still see who initiated the request and trace it back to their login. An application role masks this important piece of information from SQL Server.
0 Likes 0 ·
DirkHondong avatar image
DirkHondong answered
Just some additional information regarding loginless users: [ http://social.msdn.microsoft.com/Forums/br/sqlsecurity/thread/7d129880-048c-4834-a72e-c31ead4897cb][1] Especially the reply by Michael Hotek (author of the SQL MCTS 2008 Training Kit Book) Loginless users where planned as an alternative for application roles. So the idea is: the application (and not our Windows user) initiates the EXECUTE AS and then works in context of the loginless user. More infos here: [ http://msdn.microsoft.com/en-us/library/bb669062.aspx][2] and [ http://msdn.microsoft.com/en-us/library/bb669087.aspx][3] But as Grant and Oleg said, it's easier with database roles One last idea, but really not best practice in my eyes: create a logon trigger which checks which application is used. A few examples can be found here: [ http://www.sqlservercentral.com/Forums/Topic950142-1526-1.aspx][4] Regards and good night (I really have to go to bed now) Dirk [1]: http://social.msdn.microsoft.com/Forums/br/sqlsecurity/thread/7d129880-048c-4834-a72e-c31ead4897cb [2]: http://msdn.microsoft.com/en-us/library/bb669062.aspx+ [3]: http://msdn.microsoft.com/en-us/library/bb669087.aspx [4]: http://www.sqlservercentral.com/Forums/Topic950142-1526-1.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
You can move the EXECUTE AS code to a stored procedure that your application calls on the users' behalf and don't give out the actual LOGINLESS USER name. Anyone allowed limited access could also be put in an AD group that has DENY ALL ON VIEW DEFINITION.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.