K
Keegan Catford - HAMBS
Guest
Hi Microsoft Community,
We have a legacy application that allows access to the application via a thick client and Windows Authentication. The permissions inside of SQL Server are granted to the database via an AD group - the permissions granted is db_owner.
This gives us a few problems as there is nothing to stop AD users from connecting using SSMS, SQLCMD, ODBC etc and gaining db_owner access to production environments by default.
Re-architecting the app to prevent this behaviour is possible but is a large piece of work. We even looked into creating much more granular permissions for SQL Server and with the extra granular permissions required we almost got to db_owner anyway!
What we have attempted instead it to use 'Impersonation' at the Windows level and 'execute as login' (impersonation as well I guess) at the SQL Level with the below basic workflow.
This approach has shown a great deal of promise for most of the application operation and stops the domain users from connecting directly to the SQL Server. All tested scenarios to date have been successful except for when bulk operations are executed. The below command issues a "You do not have permissions to use the bulk load statement" error at the application level and wont continue:
(the code attempts to import an xml file from a windows UNC path)
Now every time this is attempted it fails - unless you set the database to 'trustworthy' irrespective of the SQL Server login/user permission. We have set 'mydomain\myuser', 'mydomain\servicelogin' and SQL Server Service accounts all to have permissions up to and including SYSADMIN, with no change to the error being triggered.
At this point we are at a bit of a loss as to what else we need to do as Im not terribly comfortable with setting the database to 'trustworthy' to allow this solution to work.
Anyone at all care to wade in and help us determine what else could be done here?
Thanks
Continue reading...
We have a legacy application that allows access to the application via a thick client and Windows Authentication. The permissions inside of SQL Server are granted to the database via an AD group - the permissions granted is db_owner.
This gives us a few problems as there is nothing to stop AD users from connecting using SSMS, SQLCMD, ODBC etc and gaining db_owner access to production environments by default.
Re-architecting the app to prevent this behaviour is possible but is a large piece of work. We even looked into creating much more granular permissions for SQL Server and with the extra granular permissions required we almost got to db_owner anyway!
What we have attempted instead it to use 'Impersonation' at the Windows level and 'execute as login' (impersonation as well I guess) at the SQL Level with the below basic workflow.
- User (mydomain\myuser) clicks on the executable
- The executable changes context to mydomain\servicelogin using external advapi32 APIs LogonUserA And ImpersonateLoggedOnUser
- mydomain\servicelogin has a password that is known to the application but not the user
- It has 'db_owner' permissions to the database
- It has 'impersonate any login' permissions on the SQL Server
- This means that the executable connects to SQL using mydomain\servicelogin
- Then once connected the executable uses 'execute as login mydomain\myuser' sql code.
- Then processing continues as mydomain\myuser
This approach has shown a great deal of promise for most of the application operation and stops the domain users from connecting directly to the SQL Server. All tested scenarios to date have been successful except for when bulk operations are executed. The below command issues a "You do not have permissions to use the bulk load statement" error at the application level and wont continue:
(the code attempts to import an xml file from a windows UNC path)
INSERT INTO <table> EXEC ('SELECT * FROM OPENROWSET ( BULK ######)')
Now every time this is attempted it fails - unless you set the database to 'trustworthy' irrespective of the SQL Server login/user permission. We have set 'mydomain\myuser', 'mydomain\servicelogin' and SQL Server Service accounts all to have permissions up to and including SYSADMIN, with no change to the error being triggered.
At this point we are at a bit of a loss as to what else we need to do as Im not terribly comfortable with setting the database to 'trustworthy' to allow this solution to work.
Anyone at all care to wade in and help us determine what else could be done here?
Thanks
Continue reading...