Using Windows authentication, is there a way to prohibit users from connecting via ODBC to the database?
Do you want to restrict ODBC connection to a certain database or the entire SQL instance?– Scott Hodgin - RetiredOct 23, 2019 at 16:07
both solution can work– XilmikiOct 23, 2019 at 16:19
The feature designed to help in this scenario is Application Roles. With an Application Role you can allow the user to connect to the target database, but not be able to run any queries until the application supplies a secret to activate the Application Role.
1I can never decide if this is SQL Server's worst marketed or best kept secret feature. LOL! Oct 23, 2019 at 20:21
1It’s not perfect, At least for desktop applications, as a user with a debugger can discover the Application Role secret. Oct 23, 2019 at 20:31
I have been using sql for several years. Incredibly I had never taken into consideration this feature. Thank you!– XilmikiOct 26, 2019 at 8:00
"as a user with a debugger can discover the Application Role secret"-> to solve this problem i'm planning to set app role in custom stored procedure, based on current user, at application startup so only dba can view the definition.– XilmikiOct 26, 2019 at 8:11
A possible solution is to create a server logon trigger that would check the
app_name(). I created the following server trigger on my local instance and tried to connect via ODBC. The connection was rejected. You would have to modify for your needs.
CREATE 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
A word of caution about logon triggers:
A logon trigger can effectively prevent successful connections to the Database Engine for all users, including members of the sysadmin fixed server role. When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the Database Engine in minimal configuration mode (-f)
1Logon triggers can be bypassed with the proper effort, but perhaps a login trigger in conjunction with a lookup against the
sys.dm_exec_connections(specifically looking at the
ODBCconnections) would do the trick for most scenarios. Oct 23, 2019 at 19:59
@JohnEisbrener - Thanks for the comments! Oct 23, 2019 at 20:09
1You are aware that the app name can be modified client-side right? This is absolutely not secure.– VooOct 24, 2019 at 10:27