3

Microsoft has deprecated SQL Compact so I would like to replace this with SQL LocalDB on client machines.

When using "Integrated Security = True" the replacement works fine on the client machines but once I set "Integrated Security = False" then I get

Login failed for user 'TestUser'

I can't use integrated security on the clients.
They should not be able to access the database without knowing the username / password which has already been encrypted in their machine.config.

I created a login in VS like the following:

CREATE LOGIN TestUser WITH PASSWORD = 'asdfsdfasdf';
GO
CREATE USER TestUser FOR LOGIN TestUser WITH DEFAULT_SCHEMA = [dbo];
GO 
exec sp_addrolemember 'db_owner', 'TestUser'
GO

The connection string is similar to this: 

<add name=" ConnectionStringName" 
    connectionString="Data Source=(LocalDB)\v11.0;
        AttachDbFilename=|DataDirectory|\TEST.mdf; 
        Integrated Security=False; User Id=TestUser;
        Password=asdfsdfasdf"
    providerName="System.Data.SqlClient" />

I have posted this a similar question on MSDN forums but I am not receiving good responses.
Also, it seems that there are similar questions on stackoverflow but no good responses.
LocalDB and Entity Framework 6 - Security
LocalDB connection with Integrated security=False

I have a feeling that I already know the answer in that LocalDB can't work on other computers with integrated security turned off because in theory it is using a local instance of SQL server in which the logins are stored in the master database and the clients master database would not match a login inside the LocalDB.

In my mind, that seems ludicrous that Microsoft would abandon security concerns in LocalDB by leaving integrated security turned on.
Anyone could find the database, copy it onto their computer, and then use SQL tools to read the database without even knowing a username / password.

Community
  • 1
  • 1
goroth
  • 2,510
  • 5
  • 35
  • 66
  • Can you also show how you are trying to connect to the DB? Right now your question has no C# code. – Eris Nov 21 '15 at 23:03
  • Added the connection string. There really is no C# code to show. This is simply ADO.NET connecting to the database using simple DataReader or even ExecuteScalar. They are all throwing the same error. – goroth Nov 21 '15 at 23:12
  • If that's your actual connection string. It's broken. The parans around `LocalDb` are mismatched. – Eris Nov 21 '15 at 23:16
  • @Eris Sorry, copy / paste error. – goroth Nov 21 '15 at 23:17
  • *Microsoft would abandon security concerns in LocalDB by leaving integrated security turned on* is not abandoning security concerns at all, `=true` is just as secure, if not more secure in domains then storing a password in an MDF. – Erik Philips Nov 21 '15 at 23:19
  • @ErikPhilips I'm sorry, can you explain? On my work machine that is on (Domain X), I created a LocalDB called Test.mdf and then copied the Test.mdf / Test.ldf files to my home machine (Domain Y). Then I created a simple .NET application on my home machine and was able to read the data just fine with "Integrated Security = True". So what am I missing about it not being secure? – goroth Nov 21 '15 at 23:22
  • @goroth probably because by default "local admins" have the "sysadmin" role. So any machine/person/software that connects as a local admin is basically sysadmin. – Erik Philips Nov 21 '15 at 23:27
  • You can use SQL Server Express for this, not Localdb – ErikEJ Nov 21 '15 at 23:28
  • @ErikEJ There is no way I can force all my clients to install SQL Express just because Microsoft abandon SQL Compact for a less secure product. – goroth Nov 21 '15 at 23:31
  • Looks like a duplicate of http://stackoverflow.com/questions/27138477/protect-localdb-from-user-access – Erik Philips Nov 21 '15 at 23:32
  • Is there a big difference between forcing them to install Localdb and SQL Server Express? – ErikEJ Nov 21 '15 at 23:34
  • @ErikPhilips I guess I did create a duplicate. I didn't notice this when I did my stackoverflow search. – goroth Nov 21 '15 at 23:36
  • @ErikEJ Yes there is. LocalDB was designed to only run a very small part of SQL Express (33MB) and only runs when the .NET application executes. BUT SQL Express is much bigger then 33MB and runs as a service. – goroth Nov 21 '15 at 23:38

1 Answers1

1

According to the MSDN Documentation:

You have to use integrated security when you use LocalDB or the AttachDBFileName option.

Eris
  • 7,378
  • 1
  • 30
  • 45
  • As I had suspected. Sounds like LocalDB has only designed for simple insecure applications. In my mind, SQLite would have been a much better replacement for SQL Compact. At least SQL Compact had a username / password you could add. – goroth Nov 21 '15 at 23:35
  • 1
    Not exactly. LocalDB was designed to be a developer instance with minimal overhead. It was never designed to be shipped inside an application nor replace SQL CE. – Eris Nov 21 '15 at 23:36
  • So you are saying Microsoft does not "officially" have a replacement for the deprecated SQL Compact? – goroth Nov 21 '15 at 23:40
  • I am not an employee of Microsoft, and cannot make an official statement. In my opinion, yes, that is the case. I've found several articles on using SQLite/SQLitePCL instead of SQLCE for Windows Store Apps. MS appears to be contributing more to OSS projects, and opening their own, so this is a likely path. – Eris Nov 21 '15 at 23:48
  • What entire block? The other options to Integrated Security option are not relevant to the answer. This is a direct quote from documentation, with a link to said documentation. – Eris Nov 22 '15 at 06:26