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

2 Answers 2


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.

  • 1
    I can never decide if this is SQL Server's worst marketed or best kept secret feature. LOL! Commented Oct 23, 2019 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. Commented 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!
    – Xilmiki
    Commented Oct 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.
    – Xilmiki
    Commented Oct 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.


IF APP_NAME() NOT LIKE ('Microsoft SQL Server Management Studio%')
    AND APP_NAME() NOT LIKE ('.Net SqlClient Data Provider%')

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. Commented Oct 23, 2019 at 19:59
  • @JohnEisbrener - Thanks for the comments! Commented Oct 23, 2019 at 20:09
  • 1
    You are aware that the app name can be modified client-side right? This is absolutely not secure.
    – Voo
    Commented Oct 24, 2019 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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