none
Prevent direct db access with Windows Authentication RRS feed

  • Question

  • Hi SQL Server experts,

    I have a general question concerning the security of SQL Server apps.
    We have a .NET rich client app that connects to SQL Server. We use an encrypted connection.

    If the app uses SQL Server Authentication we have to store an encrypted passwort in the app which can be read by using a memory dump after decrypting.

    So Windows Authentication seems the right way. But how can we prevent a direct db access of the user with a sql client tool (SSMS, HeidiSQL, ...)?

    Thanks a lot and have a good day,
    Ulrich


    Thursday, September 12, 2019 9:20 AM

All replies

  • Hello Ulrich,

    There is no reliable way to prevent to connect with other Tools, You could use a logon trigger to test the application name, but the client can pass by connection string any application name it likes.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 12, 2019 9:36 AM
  • To prevent this, you need to have three tiers. If you did not design your application to be three-tiered, you can still achieve this by putting in on Terminal Server or Citrix. In that case you would either retain the application login, or segment the network, so that users cannot reach SQL Server from their workstations.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, September 12, 2019 9:13 PM