October 30, 2007 at 3:04 pm
Is it possible to prevent or block ODBC connections to a specific database? We have a .Net web application that allows users to update the database. However, a user could connect to our database via ODBC and make database changes without going through the application. We want to prevent this.
The users are all members of a set of Active Directory groups which are in the db_Reader and db_Writer database roles. Our application manages and controls user access based on the group membership.
Does anyone know of a way to prevent users from connecting to a specific database via ODBC or other connections? We don't want to block ODBC access for all of the databases on this server.
We are using SQL Server 2005.
Thanks,
Kathy Davis
October 31, 2007 at 3:22 am
Application role can be a solution for your problem. See BOL for more details.
October 31, 2007 at 10:32 am
You could control this via Active Directory Group Policy Objects, by not allowing users access to the ODBC Data Source Administrator.
November 1, 2007 at 1:43 pm
Thank you for your replies. I don't think we can use Application Roles because our application accesses three databases. I read an article that said Application Roles don't work well when accessing multiple databases.
We are attempting to use a service account that has db_Reader and db_Writer roles and we will remove the existing Active Directory group privileges from the db. Then the user can access the data as the service account via the application, but they can't access it any other way. We realize that we won't be able to have any user-level auditing, but we are willing to sacrifice that.
November 1, 2007 at 10:40 pm
Kathy -
Take a look at a server trigger... you'll probably need something much more than this but here's a real simple one, when I tested using ODBC manager on my machine it blasted me right out of there...:
ALTER TRIGGER trgGetAppName
ON ALL SERVER
FOR LOGON
AS
IF APP_NAME() NOT LIKE ('Microsoft SQL Server Management Studio%') AND APP_NAME() NOT LIKE ('.Net SqlClient Data Provider%')
BEGIN
ROLLBACK
END
November 1, 2007 at 11:14 pm
Use a group policy object to control this. Before that check in a test machine if by restricting so you will be able to use the aplication successfully. Do the users have SMS installed if so you can remove them too.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 12, 2008 at 7:31 pm
The bad news:
- Any user account (Windows or SQL) granted access to a database will have the ability to connect using an ODBC connection. Users can also use scripting code to connect to a database without the need for an ODBC connection. Once connected, any rights granted to that account (db_datareader; db_datawriter; etc.) can be used to access and modify data directly.
The good news:
- You can use Windows Firewall to block inappropriate connections to a server, including your SQL server, so that only qualified systems (your web server -or- you application server) can connect on port 1433 (or a named instance port).
Microsoft notes: "When you configure a program, port, or system service exception, you should also configure scope settings for the exception. Scope settings define from which addresses incoming traffic is allowed to originate, which defines the set of computers that are allowed to send traffic for an exception."
To implement this, you will need to configure Windows Firewall: (a) create a Port exception; and (b) configure a Scope setting for your specific Port exception. So, set a port exception on 1433 and put your Web/Application server into the Scope. Any computer not in your list will be blocked.
Alternatively, or in addition:
- You can create an Application Role, and grant table/view permissions to this Role. Discussion available from Microsoft: http://msdn.microsoft.com/en-us/library/aa905193.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply