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
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