0

SQL Server 2016, encrypted with always encrypted, using certificate. In order for the client application to receive the decrypted data, it is enough to have a certificate installed on the PC, and connection string with ColumnEncryption = Enabled;.

But that means that on that PC can any application access database - just know the connection string. How do I make sure that on PC with the certificate installed - has only one / certain application access to database and his decrypted data?

TylerH
  • 20,799
  • 66
  • 75
  • 101
bmi
  • 652
  • 2
  • 10
  • 17
  • Why should you store your connection string in way everybody can access it? – sepupic Jan 19 '18 at 13:23
  • Every user who have his own sql login can fill connection parameters to application like excel or other application - it is GDPR security incident with possibility of high fine. This question is related to GDPR regulation and its support in SQL Server 2016. – bmi Jan 19 '18 at 15:00
  • Every user that has his own sql login should not have access to database. If you give the access to everyone it's your problem, not encryption problem. Of course if someone has valid login that is mapped to this db can access it bypassing your application – sepupic Jan 19 '18 at 15:14
  • sepupic - I did not say that everybody has access. sepupic - please do not answer this question more - I do not need incompetent and useless answers. We need to solve severe problem with GDPR regulation. – bmi Jan 19 '18 at 15:43

1 Answers1

0

there is a way, Application Name in Connection String .restricting-access-to-database-by-application-name-andor-host-name-in-sql-server

CREATE TRIGGER RestrictAccessPerApplication
ON ALL SERVER
FOR LOGON
AS
BEGIN
      IF
      (PROGRAM_NAME() = 'Microsoft® Access' AND HOST_NAME() = 'WORKSTATION_01')
      BEGIN
            ROLLBACK;
      END
END

But i don't think this is good for this type of sensitive applications.

Jophy job
  • 1,924
  • 2
  • 20
  • 38
  • Host name as well as application name that you see in trigger is exactly those things that application passes in its connection string. I can easily pass with SSMS any host/app name that will have nothing to do with real host and real application name – sepupic Jan 19 '18 at 17:46
  • @sepupic you are correct , that why already mentioned that in answer. But if we create connection string in code and send some particular GUID as ApplicationName . Then check that GUID in trigger ,we can achieve some security. But it all depend upon application and were it planing to deploy (Inside Organisation or public ). – Jophy job Jan 19 '18 at 18:55
  • Thanks, I will test this way - i hope it at least in part will help. But I hope, that Server 2016 functionality for GDPR regulation has possibility for application restriction, because it is essence of GDPR - to protect sensitive data. Such solution should allow some database setting, which accept `ColumnEncryption = Enabled` only for specific application. – bmi Jan 19 '18 at 20:55
  • Trigger can be removed by admin. I think that Sql Server 2016 "Always Encrypted" si unusable, because it is unable to protect data from users with valid sql login - from acces to database from another applications. But I can not believe it. – bmi Jan 25 '18 at 06:35
  • Actually this method can work, provided the Application Name is a daily computed secret. The trigger can use a CLR (obfuscted) to compute the correct daily secret. Your application should do the same and set the Application Name in the connection string. – Cogent Jan 16 '20 at 12:04