How to add username and password to sqllocaldb
I am about to start working on a new desktop application. I want to use light weight and standalone database to use SQL LocalDB but want to add authentication. There I need username and password before accessing the database, but no authentication is applied there, please help me how can I do this.
If we can't add username add password to SQL LocalDB then suggest me any other database that would be better for me and also I can use entity structure with that.
Thank you in advance
source to share
To add a new database user to yours MSSQLLocalDB
, you need to connect to it and do the following:
CREATE LOGIN your_user WITH PASSWORD = 'your_password';
CREATE USER your_user FOR LOGIN your_user;
EXEC sp_addrolemember 'db_owner', 'your_user'
You will then be able to connect to the database engine MSSQLLocalDB
using SQL Server Authentication
using these credentials.
Server name: (LocalDB)\MSSQLLocalDB
Authentication: SQL Server Authentication
User: your_user
Password: your_password
Or you can use the instance name instead of (LocalDB)\MSSQLLocalDB
like Server name
(see below for where to get it).
Initial connection to local database from SQL Server Management Studio (SSMS)
Initially, to run the above SQL command, you need to connect to MSSQLLocalDB
with Windows Authentication
. You can do this in two ways (try the second if the first won't work by default).
Using Instance Name
Server name: (LocalDB)\MSSQLLocalDB
Authentication: Windows Authentication
Using Instance Channel Name
On the command line, go to C:\Program Files\Microsoft SQL Server\130\Tools\Binn\
(you may need to use other versions and replace \130\
with your folder name) and run SqlLocalDB.exe
to find the local database instances you have:
SqlLocalDB.exe i
Make sure you have one MSSQLLocalDB
. Then run this command to see the status MSSQLLocalDB
(first line) and start if stopped (second line):
SqlLocalDB.exe i MSSQLLocalDB
SqlLocalDB.exe start MSSQLLocalDB
Then you can execute again SqlLocalDB.exe i MSSQLLocalDB
to see the channel name of the instance. Something like thatnp:\\.\pipe\LOCALDB#D7900618\tsql\query
To connect to SSMS
, you need to enter:
Server name: np:\\.\pipe\LOCALDB#D7900618\tsql\query
Authentication: Windows Authentication
source to share
use this:
SqlConnection con = new SqlConnection("Server= localhost, Authentication=Windows Authentication, Database= employeedetails");
con.Open();
if you want SQL Server Authentication like this: http://msdn.microsoft.com/en-us/library/ms162132.aspx
source to share