Previously for all our asp.net applications we have been using a sysadmin user within SQL Server to connect and add/update/delete/get data. Our SQL Admin wants to delete that account and create a Domain Account so we can use that account within our .net applications.
My current connection string is:
name="name" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password" providerName="System.Data.SqlClient"
What would the connection string be for using a domain account?
I tried:
name="name" connectionString="Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=domain\user;Password=password" providerName="System.Data.SqlClient"
and it does not work.
Is there a different way to connect to SQL Server using a domain account?
One workaround is to create a user ID in the server's Microsoft Windows domain with the same user name and password as the requesting user ID. Then, when SQL Server using named pipes to authenticate, the it succeeds.
Right-click on your connection and select "Properties". You will get the Properties window for your connection. Find the "Connection String" property and select the "connection string". So now your connection string is in your hands; you can use it anywhere you want.
Have a look at connectionstrings.com for every possible variation - a very handy resource I use all the time
Specifically, you want this format:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
This, of course, only works if the domain account in question is the one opening the connection.
There's no easy way to connect with arbitrary credentials - but you can impersonate the user in question and then connect.
This can be a bit of a pain. An alternative if the users are on the local network (or you control their browser config) is to use Kerberos authentication on your site. The pages will be served with the relevant user's permissions - then you can use the connection string above and IIS will connect to the Db with the appropriate credentials for each user. This is particularly useful from a security perspective as the Db is able to audit on a per-user basis, and permissions can be per-user/row/column instead of only per-app.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With