SQL Server - Restricting Access
Question
Friday, February 19, 2010 8:57 AM
Using Access projects I am connecting to a SQL database usine connections. The front is using bound forms, this required users to have read and right access to tables and/or views through AD security groups. While I can sufficently control user interaction. However, I need to be able to block users that attempt to connect through Excel/ODBC etc, who can now do considerable damage.
Can I further restrict SQL connections to ensure that the database will only allow connections from the Access Front-end, the web application and Reporting services.
Any Ideas?
Thanks,
David.
All replies (6)
Monday, February 22, 2010 6:32 AM ✅Answered
Hi,
SQL Server doesn’t support any permission principal that is based on the application type from the client connection. The SQL Server permissions hierarchy is Windows level, SQL Server level and Database level. To work around the issue, you could consider one of the following two ways:
- To avoid connection through Excel/ODBC, the way is to disable or not create the logins for the users that attempt to connect SQL Server through Excel/ODBC. Then, create the special logins for the users that connect SQL Server from the Access Front-end, the web application and Reporting Services respectively.
2. Define the application roles. After connecting to the server, activate the application role. Please refer to :
http://msdn.microsoft.com/en-us/library/ms190998.aspx
http://msdn.microsoft.com/en-us/library/ms188908.aspx
http://articles.techrepublic.com.com/5100-10878_11-5068954.html
If there are any more questions, please let me know.
Thanks.
***Xiao Min Tan***Microsoft Online Community***
Thursday, March 4, 2010 8:18 AM
Thanks Xiao,
This has lead me to review a login trigger at server level. This seems to satisfy my needs, but a little more testing is required. I am now able to restrict connections based on a mix of AD security groups and the application being used to connect to the server. I have had to add in a get out clause to ensure that administrators/service accounts ect are never blocked.
All the best,
David.
Wednesday, August 11, 2010 2:31 AM
Hi,
SQL Server doesn’t support any permission principal that is based on the application type from the client connection. The QL Server permissions hierarchy is Windows level, SQL Server level and Database level. To work around the issue, you could consider one of the following two ways:
- To avoid connection through Excel/ODBC, the way is to disable or not create the logins for the users that attempt to connect SQL Server through Excel/ODBC. Then, create the special logins for the users that connect SQL Server from the Access Front-end, the web application and Reporting Services respectively.
2. Define the application roles. After connecting to the server, activate the application role. Please refer to :
http://msdn.microsoft.com/en-us/library/ms190998.aspx
http://msdn.microsoft.com/en-us/library/ms188908.aspx
http://articles.techrepublic.com.com/5100-10878_11-5068954.htmlIf there are any more questions, please let me know.
Thanks.
***Xiao Min Tan***Microsoft Online Community***
I'm new here, Now I have a more clear idea about it. Thx.
Wednesday, August 11, 2010 4:50 AM
Hi,
SQL Server doesn’t support any permission principal that is based on the application type from the client connection. The QL Server permissions hierarchy is Windows level, SQL Server level and Database level. To work around the issue, you could consider one of the following two ways:
- To avoid connection through Excel/ODBC, the way is to disable or not create the logins for the users that attempt to connect SQL Server through Excel/ODBC. Then, create the special logins for the users that connect SQL Server from the Access Front-end, the web application and Reporting Services respectively.
2. Define the application roles. After connecting to the server, activate the application role. Please refer to :
http://msdn.microsoft.com/en-us/library/ms190998.aspx
http://msdn.microsoft.com/en-us/library/ms188908.aspx
http://articles.techrepublic.com.com/5100-10878_11-5068954.htmlIf there are any more questions, please let me know.
Thanks.
***Xiao Min Tan***Microsoft Online Community***
I'm new here, Now I have a more clear idea about it. Thx.
Monday, October 11, 2010 6:56 PM
Hello David,
I have the exact same issue, now I'm very interested in how did you handled the excel users trying to connect to SQL Server. Can you explain a little bit about login trigger at server level? or give a link about the topic?
regards,
Vegueta
Monday, October 11, 2010 7:20 PM
Hello David,
I have the exact same issue, now I'm very interested in how did you handled the excel users trying to connect to SQL Server. Can you explain a little bit about login trigger at server level? or give a link about the topic?
regards,
Vegueta
Information about Logon trigger is here http://msdn.microsoft.com/en-us/library/bb326598.aspx and you can use the example here to stop users accessing from EXCEL application.Thanks, Leks