How to add username and password in sqllocaldb

Question

I am going to start work on new Desktop application. There is I want to use light weight and standalone database so that I am going use SQL LocalDB but I want to add authentication. There I need username and password before accessing database but authentication not applied there please help me how can I do it.

If we cannot add username add password in SQL LocalDB then please suggest me any another database that will best for me and also I can use entity framework with that.

Thanks in advance

Answer 1

To add your new DB user to your MSSQLLocalDB you need to connect to it and execute this:

CREATE LOGIN your_user WITH PASSWORD = 'your_password';
CREATE USER your_user FOR LOGIN your_user;
EXEC sp_addrolemember 'db_owner', 'your_user'

Then you will be able to connect to MSSQLLocalDB database engine with SQL Server Authentication using these credentials.

Server name: (LocalDB)\MSSQLLocalDB
Authentication: SQL Server Authentication
User: your_user
Password: your_password

Or you can use instance pipe name instead of (LocalDB)\MSSQLLocalDB as a Server name (see below where to get it).

Initial connection to your local DB from SQL Server Management Studio (SSMS)

Initially to run the SQL command above you need to connect to your MSSQLLocalDB with Windows Authentication. You can do it in two ways (try the second if the first one won't work by default).

Using instance name

Server name: (LocalDB)\MSSQLLocalDB
Authentication: Windows Authentication

Using instance pipe name

From the command line go to C:\Program Files\Microsoft SQL Server\130\Tools\Binn\ (you might need to use other versions and replace \130\ with your folder name) and run SqlLocalDB.exe to find the local DB instances you have:

SqlLocalDB.exe i 

Make sure you have MSSQLLocalDB listed. Then run this command to see the MSSQLLocalDB status (the first line) and start if it's stopped (the second line):

SqlLocalDB.exe i MSSQLLocalDB
SqlLocalDB.exe start MSSQLLocalDB

Then you can execute SqlLocalDB.exe i MSSQLLocalDB again to see the the instance pipe name. Something like this np:\\.\pipe\LOCALDB#D7900618\tsql\query

To connect in SSMS you need to enter:

Server name: np:\\.\pipe\LOCALDB#D7900618\tsql\query
Authentication: Windows Authentication