4

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 Oct 23 at 16:07
  • both solution can work – Xilmiki Oct 23 at 16:19
7

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.

  • I can never decide if this is SQL Server's worst marketed or best kept secret feature. LOL! – John Eisbrener Oct 23 at 20:21
  • 1
    It’s not perfect, At least for desktop applications, as a user with a debugger can discover the Application Role secret. – David Browne - Microsoft Oct 23 at 20:31
  • I have been using sql for several years. Incredibly I had never taken into consideration this feature. Thank you! – Xilmiki Oct 26 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. – Xilmiki Oct 26 at 8:11
7

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)

  • 1
    Logon 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 client_interface_name column for ODBC connections) would do the trick for most scenarios. – John Eisbrener Oct 23 at 19:59
  • @JohnEisbrener - Thanks for the comments! – Scott Hodgin Oct 23 at 20:09
  • You are aware that the app name can be modified client-side right? This is absolutely not secure. – Voo Oct 24 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.