1

I have written a Windows application in C# that connects to a SQL Server database. I run this query from inside the application to create SQL Server user authentication that is presumably hidden from PC user: 

SET @LoginName = 'TestAdmin'
SET @Password = 'pass@word1'

-- Create user
SET @SQL = 'Create Login ' + @LoginName + ' with Password = ''' + @Password + ''''

EXEC sp_executesql @SQL

-- Adding user to sysadmin role
EXEC sp_addsrvrolemember @LoginName, 'sysadmin'

I am planning to upload a .SQL file to create database with tables and data, all that to be done programmatically from the application. I do not want application user or PC owner to have full access to this database.

The problem:

When I go to SQL Server Management Studio, as a user, I can simply connect to database using Windows authentication and have full access to database tables and data! How can I prevent that? I want only that application to have access to its database?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Hussein
  • 653
  • 3
  • 10
  • 28
  • 3
    Not possible unless you host the database yourself. A sysadmin can always view your database. You can create roles for your app. and lock down to those roles. You can also encrypt your procs, but it won't keep a sysadmin out – Mitch Wheat May 06 '20 at 08:15
  • 2
    That said, if you took control of the users server you could install keys/certs, encrypt the master DB etc and lock the user out of their own server, but what user in his/her right mind would allow your app to do that? You would have to install your own instance of SQLExpress and it's non-trivial to lock down – Mitch Wheat May 06 '20 at 08:22
  • 1
    You need to provide more details. Is this application something your users download and install themselves? Then you cannot properly hide a SQL Server installation and database from them. Do you instead maybe want your application to connect to a web service instead? Then you can host the database yourself, and control what your users can and can't do with that database. – CodeCaster May 06 '20 at 08:26
  • @MitchWheat Yes, I want the application to be installable. I understand what you said, but I thought there is way to install SQL Server on PC locally and hide its credentials. – Hussein May 06 '20 at 08:33

1 Answers1

1

I understand that it is some internal database of your application? Is that right? That assumes, that the client of the application has the Sql server installed (which is risky assumption)

Nevertheless, if you want the data to be accessible only for your application i would suggest using SqlLite database (database as a file in your application folder)

You can password protect it as described here: Password Protect a SQLite DB. Is it possible?

Of course, if you want to prevent tampering you would have to think about how to store the password. If you do, that in code - it will be accessible for seeing after decompilation. Then you would have to think about code obfuscation. Better would be to get the key from the network.

Piotr
  • 1,155
  • 12
  • 29
  • 2
    And then your user uses a network sniffer and we're back to square zero. – CodeCaster May 06 '20 at 08:27
  • @CodeCaster the only way to secure data is by hosting the database myself? – Hussein May 06 '20 at 08:35
  • @CodeCaster - if the database is local - about what sniffing you are talking about? – Piotr May 06 '20 at 08:39
  • @Hussein - no it is not "the only way" But using Sql server of local machine for some application you want to ship - sounds strange;) – Piotr May 06 '20 at 08:39
  • The network sniffing part of course is about _"Better would be to get the key from the network"_. – CodeCaster May 06 '20 at 11:07
  • @CodeCaster - Ahh, okay. But then you can use encryption. Nevertheless, for the case provided, doing it in code is "enough" i think ;) – Piotr May 06 '20 at 11:12
  • And then the user uses a debugger and gets the password from memory. Or uses a man-in-the-middle attack. Anything your application can do, a malicious user can do. – CodeCaster May 06 '20 at 15:56
  • Then you use SecureString. But tbh - i don't know why we have this discussion?:D If someone wants to break application, most probably with proper set of skills - he will. But having two scenarions - 1) where you force the user to install sql server and 2) you have local DB with your application - i suppose option 2) is better. Of course - option 3) You install your own SQL Server (or any other SQL or non-SQL database) is maybe better option. But i feel, like it is not an option for @Hussein. If it is - i strongly recomend it. If it is not - i stand by my proposal. – Piotr May 06 '20 at 16:11