0

I've to implement the following requirement:

Access to SQL Server instance shall be allowed only from a C# application. Users shall not be able to access any database (even those in which they have access) via SQLCMD, SSMS. Access using SSMS shall be allowed only for logins that are sysadmin, serveradmin. Connection from one specific host machine shall be denied. Connections from a specific C# application shall be allowed.

Is it possible, secure and safe to implement this via logon trigger? If yes, how can I implement it in a reliable manner.

In order to apply the logon trigger, is it necessary to be done via SSMS, only once to be activated. In case something goes wrong how can I disable it?

0
3

You can try to do this...

CREATE TRIGGER CSharpOnly ON ALL SERVER FOR LOGON
AS
BEGIN
     IF APP_NAME() NOT IN (N'Your C# app_name() according to SQL Server')
     BEGIN
          RAISERROR('Please use sea sharp.', 16, 1);
          ROLLBACK;
     END
END

...but, since application name and other connection properties are easily spoofed, it is very easy for your users to bypass them - they can just tell SSMS, for example, that it should present itself as the same APP_NAME(). So, it all depends on your definition of "in a reliable manner."

When things go south

In order to fix a bad trigger, you can bypass logon triggers entirely by using the Dedicated Administrator Connection (DAC) to connect and modify / disable the trigger. You can also keep your initial connection established when you create / enable the trigger - existing connections won't go through the trigger again unless they get disconnected.

2
  • Is any way possible to avoid to be spoofed ? – Elena2020 Feb 4 at 18:22
  • @Elena2020 Sure, use group policy to prevent users from installing any applications except your C# app. Like I said, you can try. The other thing you can do is don't give users the credentials that the app uses, and lock down the database for any other login - have the app retrieve them from a secure vault of some kind. Much harder (maybe impossible?) to spoof a login. – Aaron Bertrand Feb 4 at 18:57
1

Maybe this would be a better approach than a Logon Trigger. Instead, create the Logins and correlating Database Users for your domain users who should have access to the database via your C# app. Set the permissions on the Database Users appropriately. Disable their Logins on the server. Create a SQL Login and Database User dedicated for your C# application that has the highest level permissions any domain user would theoretically need.

In your C# app, use the dedicated SQL Login account to connect to your database. Use Windows Authentication to get the current domain user running the application. Whenever you make a database call, prefix the SQL code being executed with EXECUTE AS USER = followed by the Database User that corresponds to that domain user (the names should theoretically match exactly). For example EXECUTE AS USER = 'JD'; SELECT SomeField FROM SomeTable. Then the permissions you setup on your SQL instance will be applied for that database call to your dedicated SQL Database User, and you get your security enforcement and no domain user will be able to connect to your SQL instance any other way.

I also think this helps reduce the chances of a spoofing issue like Aaron Bertrand pointed out, via obscurity, since a domain user would need to know the name of the dedicated SQL Login (and password for that matter) to be able to connect to the server. And the only way they'd be able to do that is if they had the source code to your app. But even then, if you implement secure connection strings in your application (depending on the database framework you're using) theoretically the password should still be secured.

Downsides to this are it may be confusing to developers who are inexperienced with SQL Server on how the security permissions are being enforced, and if you want to use AD Groups for your Database Users then you need to implement an additional call in your C# code to look up the AD Groups that correspond to the current domain user running the app, before you EXECUTE AS that AD group's correlating Database User. There's probably gotta be other reasons this isn't necessarily the best practice from a security model perspective or a code maintainability perspective, but seems like a nifty solution to your problem anyway.

3
  • Why is it necessary to disable their logins on server? And how can I do that? – Elena2020 Feb 8 at 7:17
  • @Elena2020 If you disable the Logins on the SQL Server for your domain users (either via an AD Group Login or the individual user Logins) then those domain users will never be able to connect to your SQL Server other than via your application (like you want) with a separate dedicated Login you use via the application. Using SQL Server Management Studio, under the Security folder you should see all the Logins and should be able to right click on any of them, and click "Disable". – J.D. Feb 8 at 12:57
  • The reasoning for creating the Logins and Database Users for your domain users in the first place is because even though those Logins will be disabled and your domain users won't be able to connect to the SQL Server (other than via your application), the dedicated (enabled) SQL Login (that your application will use) can impersonate the Database Users of your domain users when your application executes a SQL query (by using EXECUTE AS) which will enforce the proper database permissions. – J.D. Feb 8 at 13:06
0

I think you could do something using the SQL 2017 feature "Always Encrypted with secure enclaves" which allows clients with the right certificate to access the encrypted data, presumably you'd then elevate the privileges / use an application role to access the database while keeping all ordinary users DENYied from using tables or SPs. I've never worked with this feature though & am not very strong at encryption - so this is only half an answer, maybe someone else can add the other (more difficult) half ?

1
  • Unfortunately I'm using sql server express 2014. – Elena2020 Feb 4 at 19:21

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.