CODEWITHSUNDEEP
c#sql-serverwindows-authenticationsqlconnection
Iraj
Iraj

Reputation: 389

How to make a connection using windows authentication for working with multiple databases?

I previously used SQL connection for my SQL queries in C# like this:

SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"Data Source=our.server;" +
"User id=user_ID; " +
"Password=The_Pass; " +
"MultipleActiveResultSets = true";

conn.Open();

But it is not working when I want to work with multiple databases at once.

//just a very simplified representation of my very complicated query
string double_db_query = " select top 10 * into [DB_1].[dbo].[test_table] from [DB_2].[dbo].[source_table] "; 
using (SqlCommand double_db_command = new SqlCommand(double_db_query, conn)) 
{
double_db_command.ExecuteNonQuery();
}

It returns error because my connection is only connected to one of the databases I have separate usernames and passwords for each DB and I can't be connected to two DB by SQL connection. My windows account has access to all the databases but I don't know how to make a windows connection. The samples that I have seen all include database name which shows they connect to only one database. Like in this link: https://www.connectionstrings.com/sql-server/

Can a windows authentication completely replace my SQL connection to be used in making commands? Related question: Connecting to SQL Server using windows authentication

Upvotes: 0

Views: 1828

Answers (1)

Randy in Marin
Randy in Marin

Reputation: 1143

For windows authentication you want to use what's called "integrated security". You can search online for the term or go to the following for examples. You don't specify either a user or password. Something like "Trusted_Connection=True;" is used instead.

https://www.connectionstrings.com/sql-server/

The query from multiple database on the same instance is fine. The account used to run the query must have the required permissions to both databases. If you are not too worried about access, then you can map the windows account as users to both databases and add the user in each to db_owner.

You can do the same for a sql server login. It can be mapped as users in both databases. Granting permissions to databases is the same for Windows accounts/groups and for SQL Server logins.

Upvotes: 1

Related Questions