Sql server authentication connection string windows authentication - Доктор Windows

I am creating a website, but in the database I use windows authentication.

I know that you use this for SQL authentication

<connectionStrings> 
    <add name="NorthwindContex" 
       connectionString="data source=localhost;
       initial catalog=northwind;persist security info=True; 
       user id=sa;password=P@ssw0rd" 
       providerName="System.Data.SqlClient" /> 
</connectionStrings>

How do I modify this to work with windows authentication?

Jono

3,8824 gold badges26 silver badges48 bronze badges

asked Jan 11, 2013 at 1:07

Replace the username and password with Integrated Security=SSPI;

So the connection string should be

<connectionStrings> 
<add name="NorthwindContex" 
   connectionString="data source=localhost;
   initial catalog=northwind;persist security info=True; 
   Integrated Security=SSPI;" 
   providerName="System.Data.SqlClient" /> 
</connectionStrings>

Sami

8,0548 gold badges66 silver badges97 bronze badges

answered Jan 11, 2013 at 1:13

heads5150heads5150

7,1533 gold badges25 silver badges35 bronze badges

For connecting to a sql server database via Windows authentication basically needs which server you want to connect , what is your database name , Integrated Security info and provider name.

Basically this works:

<connectionStrings>      
<add name="MyConnectionString"
         connectionString="data source=ServerName;
   Initial Catalog=DatabaseName;Integrated Security=True;"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Setting Integrated Security field true means basically you want to reach database via Windows authentication, if you set this field false Windows authentication will not work.

It is also working different according which provider you are using.

SqlClient both Integrated Security=true; or IntegratedSecurity=SSPI; is working.
OleDb it is Integrated Security=SSPI;
Odbc it is Trusted_Connection=yes;
OracleClient it is Integrated Security=yes;

Integrated Security=true throws an exception when used with the OleDb provider.

answered Mar 6, 2019 at 9:08

nzrytmnnzrytmn

5,7131 gold badge41 silver badges37 bronze badges

For the correct solution after many hours:

Open the configuration file
Change the connection string with the following

<add name="umbracoDbDSN" connectionString="data source=YOUR_SERVER_NAME;database=nrc;Integrated Security=SSPI;persist security info=True;" providerName="System.Data.SqlClient" />

Change the YOUR_SERVER_NAME with your current server name and save
Open the IIS Manager
Find the name of the application pool that the website or web application is using
Right-click and choose Advanced settings
From Advanced settings under Process Model change the Identity to Custom account and add your Server Admin details, please see the attached images:

Hope this will help.

answered Dec 13, 2017 at 12:33

AhmedBinNasserAhmedBinNasser

2,7251 gold badge23 silver badges23 bronze badges

This is shorter and works

<connectionStrings>      
<add name="DBConnection"
             connectionString="data source=SERVERINSTANCE;
       Initial Catalog=MyDB;Integrated Security=SSPI;"
             providerName="System.Data.SqlClient" />
</connectionStrings>

Persist Security Info not needed

answered Sep 26, 2017 at 15:45

CREMCREM

1,9311 gold badge27 silver badges35 bronze badges

If anyone comes looking for asp.net core, we will have to add connection string in appsettings.json

 {
"ConnectionStrings": {
   "DefaultConnection": "Server=SQLServer\Instance;Database=MYDB;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Source: add windows authentication sql server connection string

answered Jun 22, 2022 at 7:21

Источник

Microsoft SqlClient Data Provider for SQL Server

Standard Security

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connection to a SQL Server instance

The server/instance name syntax used in the server option is the same for all SQL Server connection strings.

Server=myServerNamemyInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Server=myServerName,myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Enable MARS

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;MultipleActiveResultSets=true;

Attach a database file on connect to a local SQL Server Express instance

Server=.SQLExpress;AttachDbFilename=C:MyFolderMyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

User Instance on local SQL Server Express

The User Instance feature is deprecated with SQL Server 2012, use the SQL Server Express LocalDB feature instead.

LocalDB automatic instance

Server=(localdb)v11.0;Integrated Security=true;

LocalDB automatic instance with specific data file

Server=(localdb)v11.0;Integrated Security=true;AttachDbFileName=C:MyFolderMyData.mdf;

LocalDB named instance

To create a named instance, use the SqlLocalDB.exe program. Example SqlLocalDB.exe create MyInstance and SqlLocalDB.exe start MyInstance

Server=(localdb)MyInstance;Integrated Security=true;

LocalDB named instance via the named pipes pipe name

The Server=(localdb) syntax is not supported by .NET framework versions before 4.0.2. However the named pipes connection will work to connect pre 4.0.2 applications to LocalDB instances.

Server=np:\.pipeLOCALDB#F365A78Etsqlquery;

LocalDB shared instance

Both automatic and named instances of LocalDB can be shared.

Server=(localdb).MyInstanceShare;Integrated Security=true;

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

Asynchronous processing

A connection to SQL Server that allows for the issuing of async requests through ADO.NET objects.

Server=myServerAddress;Database=myDataBase;Integrated Security=True;Asynchronous Processing=True;

Using an User Instance on a local SQL Server Express instance

The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer.

Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=C:MyFolderMyDataFile.mdf;User Instance=true;

Specifying packet size

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096;

Always Encrypted

Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;Column Encryption Setting=enabled;

Always Encrypted with secure enclaves

Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;Column Encryption Setting=enabled;Enclave Attestation Url=http://hgs.bastion.local/Attestation;

.NET Framework Data Provider for SQL Server

Standard Security

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connection to a SQL Server instance

The server/instance name syntax used in the server option is the same for all SQL Server connection strings.

Server=myServerNamemyInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Server=myServerName,myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection from a CE device

A Windows CE device is most often not authenticated and logged in to a domain but it is possible to use SSPI or trusted connection and authentication from a CE device using this connection string.

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomainmyUsername;Password=myPassword;

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Enable MARS

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;MultipleActiveResultSets=true;

Attach a database file on connect to a local SQL Server Express instance

Server=.SQLExpress;AttachDbFilename=C:MyFolderMyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

User Instance on local SQL Server Express

The User Instance feature is deprecated with SQL Server 2012, use the SQL Server Express LocalDB feature instead.

LocalDB automatic instance

Server=(localdb)v11.0;Integrated Security=true;

LocalDB automatic instance with specific data file

Server=(localdb)v11.0;Integrated Security=true;AttachDbFileName=C:MyFolderMyData.mdf;

LocalDB named instance

To create a named instance, use the SqlLocalDB.exe program. Example SqlLocalDB.exe create MyInstance and SqlLocalDB.exe start MyInstance

Server=(localdb)MyInstance;Integrated Security=true;

LocalDB named instance via the named pipes pipe name

The Server=(localdb) syntax is not supported by .NET framework versions before 4.0.2. However the named pipes connection will work to connect pre 4.0.2 applications to LocalDB instances.

Server=np:\.pipeLOCALDB#F365A78Etsqlquery;

LocalDB shared instance

Both automatic and named instances of LocalDB can be shared.

Server=(localdb).MyInstanceShare;Integrated Security=true;

Database mirroring

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

Asynchronous processing

A connection to SQL Server that allows for the issuing of async requests through ADO.NET objects.

Server=myServerAddress;Database=myDataBase;Integrated Security=True;Asynchronous Processing=True;

Using an User Instance on a local SQL Server Express instance

Data Source=.SQLExpress;Integrated Security=true;AttachDbFilename=C:MyFolderMyDataFile.mdf;User Instance=true;

Specifying packet size

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;Packet Size=4096;

Always Encrypted

Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;Column Encryption Setting=enabled;

Always Encrypted with secure enclaves

Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;Column Encryption Setting=enabled;Enclave Attestation Url=http://hgs.bastion.local/Attestation;

Microsoft OLE DB Driver for SQL Server

Standard security

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

ADO to map new data types

For ADO to correctly map SQL Server new datatypes, i.e. XML, UDT, varchar(max), nvarchar(max), and varbinary(max), include DataTypeCompatibility=80; in the connection string. If you are not using ADO this is not necessary.

Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted connection

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=MSOLEDBSQL;Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Provider=MSOLEDBSQL;Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

Enable MARS

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS Connection=true;

Encrypt data sent over network

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=MSOLEDBSQL;Server=.SQLExpress;AttachDBFilename=c:asdqwemydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Provider=MSOLEDBSQL;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

Availability group and failover cluster

Enable fast failover for Always On Availability Groups and Failover Cluster Instances. TCP is the only supported protocol. Also set an explicit timeout as these scenarios might require more time.

Provider=MSOLEDBSQL;Server=tcp:AvailabilityGroupListenerDnsName,1433;MultiSubnetFailover=Yes;Database=MyDB;Integrated Security=SSPI;Connect Timeout=30;

Read-Only application intent

Use a read workload when connecting. Enforces read only at connection time, and also for USE database statements.

Provider=MSOLEDBSQL;Server=tcp:AvailabilityGroupListenerDnsName,1433;MultiSubnetFailover=Yes;ApplicationIntent=ReadOnly;Database=MyDB;Integrated Security=SSPI;Connect Timeout=30;

Read-Only routing

You can either use an availability group listener for Server OR the read-only instance name to enforce a specific read-only instance.

Provider=MSOLEDBSQL;Server=aKnownReadOnlyInstance;MultiSubnetFailover=Yes;ApplicationIntent=ReadOnly;Database=MyDB;Integrated Security=SSPI;Connect Timeout=30;

SQL Server Native Client 11.0 OLE DB Provider

Standard security

Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted connection

Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=SQLNCLI11;Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Provider=SQLNCLI11;Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways





  


                       
  




oConn.Open "Provider=SQLNCLI11;Server=myServerAddress;DataBase=myDataBase;"

Enable MARS

Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS Connection=True;

Encrypt data sent over network

Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=SQLNCLI11;Server=.SQLExpress;AttachDbFilename=c:asdqwemydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Provider=SQLNCLI11;Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Provider=SQLNCLI11;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

SQL Server Native Client 10.0 OLE DB Provider

Standard security

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted connection

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=SQLNCLI10;Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Provider=SQLNCLI10;Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Provider=SQLNCLI10;Server=myServerAddress;DataBase=myDataBase;"

Enable MARS

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS Connection=True;

Encrypt data sent over network

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=SQLNCLI10;Server=.SQLExpress;AttachDbFilename=c:asdqwemydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Provider=SQLNCLI10;Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Provider=SQLNCLI10;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

SQL Native Client 9.0 OLE DB Provider

Standard security

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted connection

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=SQLNCLI;Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Provider=SQLNCLI;Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Provider=SQLNCLI;Server=myServerAddress;DataBase=myDataBase;"

Enable MARS

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS Connection=True;

Encrypt data sent over network

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=SQLNCLI;Server=.SQLExpress;AttachDbFilename=c:mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Provider=SQLNCLI;Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Provider=SQLNCLI;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

Microsoft OLE DB Provider for SQL Server

Standard Security

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Trusted connection

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Provider=sqloledb;Data Source=myServerNametheInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Provider=sqloledb;Server=myServerName,myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;

Prompt for username and password

This one is a bit tricky. First set the connection object’s Provider property to «sqloledb». Thereafter set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Provider = "sqloledb" oConn.Properties("Prompt") = adPromptAlways


          oConn.Open "Data Source=myServerAddress;Initial Catalog=myDataBase;"

Connect via an IP address

Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Disable connection pooling

This one is usefull when receving errors «sp_setapprole was not invoked correctly.» (7.0) or «General network error. Check your network documentation» (2000) when connecting using an application role enabled connection. Application pooling (or OLE DB resource pooling) is on by default. Disabling it can help on this error.

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;OLE DB Services=-2;

.NET Framework Data Provider for OLE DB

Microsoft ODBC Driver 17 for SQL Server

Standard security

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted Connection

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={ODBC Driver 17 for SQL Server};Server=serverNameinstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={ODBC Driver 17 for SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

Enable MARS

Driver={ODBC Driver 17 for SQL Server};Server=serverAddress;Database=databaseName;Trusted_Connection=yes;MARS_Connection=yes;

Encrypt data sent over network

Driver={ODBC Driver 17 for SQL Server};Server=serverAddress;Database=databaseName;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={ODBC Driver 17 for SQL Server};Server=.SQLExpress;AttachDBFileName=c:dir\mydb.mdf;Database=dbName;Trusted_Connection=yes;

Database mirroring

If you connect to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

Microsoft ODBC Driver 13 for SQL Server

Standard security

Driver={ODBC Driver 13 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted Connection

Driver={ODBC Driver 13 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={ODBC Driver 13 for SQL Server};Server=serverNameinstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={ODBC Driver 13 for SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

Enable MARS

Driver={ODBC Driver 13 for SQL Server};Server=serverAddress;Database=databaseName;Trusted_Connection=yes;MARS_Connection=yes;

Encrypt data sent over network

Driver={ODBC Driver 13 for SQL Server};Server=serverAddress;Database=databaseName;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={ODBC Driver 13 for SQL Server};Server=.SQLExpress;AttachDBFileName=c:dir\mydb.mdf;Database=dbName;Trusted_Connection=yes;

Database mirroring

Driver={ODBC Driver 13 for SQL Server};Server=myServerAddress;Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

Microsoft ODBC Driver 11 for SQL Server

Standard security

Driver={ODBC Driver 11 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted Connection

Driver={ODBC Driver 11 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={ODBC Driver 11 for SQL Server};Server=serverNameinstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={ODBC Driver 11 for SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

Enable MARS

Driver={ODBC Driver 11 for SQL Server};Server=serverAddress;Database=databaseName;Trusted_Connection=yes;MARS_Connection=yes;

Encrypt data sent over network

Driver={ODBC Driver 11 for SQL Server};Server=serverAddress;Database=databaseName;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={ODBC Driver 11 for SQL Server};Server=.SQLExpress;AttachDBFileName=c:dir\mydb.mdf;Database=dbName;Trusted_Connection=yes;

Database mirroring

Driver={ODBC Driver 11 for SQL Server};Server=myServerAddress;Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

SQL Server Native Client 11.0 ODBC Driver

Standard security

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted Connection

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={SQL Server Native Client 11.0};Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={SQL Server Native Client 11.0};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;"

Enable MARS

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS_Connection=yes;

Encrypt data sent over network

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Server Native Client 11.0};Server=.SQLExpress;AttachDbFilename=c:asdqwemydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Server Native Client 11.0};Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Driver={SQL Server Native Client 11.0};Server=myServerAddress;Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

SQL Server Native Client 10.0 ODBC Driver

Standard security

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted Connection

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={SQL Server Native Client 10.0};Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={SQL Server Native Client 10.0};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;"

Enable MARS

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS_Connection=yes;

Encrypt data sent over network

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Server Native Client 10.0};Server=.SQLExpress;AttachDbFilename=c:asdqwemydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Server Native Client 10.0};Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

SQL Native Client 9.0 ODBC Driver

Standard security

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted Connection

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.

Driver={SQL Native Client};Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={SQL Native Client};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;"

Enable MARS

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS_Connection=yes;

Encrypt data sent over network

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.SQLExpress;AttachDbFilename=c:mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

Database mirroring

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Failover_Partner=myMirrorServerAddress;Database=myDataBase;Trusted_Connection=yes;

Microsoft SQL Server ODBC Driver

Standard Security

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Trusted connection

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;

Using a non-standard port

If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it’s not a colon).

Driver={SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object’s Prompt property to adPromptAlways. Then use the connection string to connect to the database.

oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Driver={SQL Server};Server=myServerAddress;Database=myDataBase;"

.NET Framework Data Provider for ODBC

SQLXML 4.0 OLEDB Provider

SQLXML 3.0 OLEDB Provider

Using SQL Server Ole Db

The SQLXML version 3.0 restricts the data provider to SQLOLEDB only.

Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Context Connection

Connecting to «self» from within your CLR stored prodedure/function. The context connection lets you execute Transact-SQL statements in the same context (connection) that your code was invoked in the first place.

C# using(SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); // Use the connection }


            VB.Net

             Using connection as new SqlConnection("context connection=true")

                 connection.Open()

                 ' Use the connection

             End Using

MSDataShape

Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=myServerAddress;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Источник

In this article I will explain with an example, how to set and read SQL Server Connection String for Windows Authentication in Web.Config file in ASP.Net using C# and VB.Net.

In ASP.Net Web Applications, one has to reference the System.Configuration Assembly in order to read the SQL Server Connection String for Windows Authentication from the ConnectionStrings section of the Web.Config file.

SQL Server Connection String for Windows Authentication in Web.Config file

SQL Server Connection String for Windows Authentication in Web.Config file is defined as follows and it consists of the following properties.

Data Source

— The name of the SQL Server and its Instance.
Initial Catalog — The name of the Database.
Integrated Security — By default False. If set true it signifies that Windows Authentication needs to be used.

</connectionStrings>

</configuration>

Adding System.Configuration reference

In order to access the SQL Server Connection String for Windows Authentication from Web.Config file in code behind, the very first thing you need to do is to add reference of the System.Configuration Assembly to the project in the following way.

1. Right click on the project and click Add Reference option from the Context Menu.

2. From the Add Reference dialog box, click on .Net Tab and look for System.Configuration assembly. Once you find it simply select and click OK.

3. Once the reference is added, it will be shown in the References folder of the Solution Explorer.

Namespaces

You will need to import the following namespace.

using System.Configuration;

VB.Net

Imports System.Configuration

Reading ConnectionString from Web.Config file in ASP.Net using C# and VB.Net

Once the reference is added, you can read the SQL Server Connection String for Windows Authentication from the Web.Config file in the following way.

string connectionString = ConfigurationManager.ConnectionStrings[«ConString»].ConnectionString;

VB.Net

Dim connectionString As String = ConfigurationManager.ConnectionStrings(«ConString»).ConnectionString

Download Free Word/PDF/Excel API

Comments

No comments have been added to this article.

Источник

This article intends to give some useful tips on usage details of the SQL connection strings.

What are SQL connection strings?

The connection string is an expression that contains the parameters required for the applications to connect a
database server. In terms of SQL Server, connection strings include the server instance, database name,
authentication details, and some other settings to communicate with the database server.

How to connect SQL Server using a connection string

We can use the following connection string for the SQL Server authentication. In this type of connection string, we
require to set the user name and password.

Server=ServerName;Database=DatabaseName;User Id=UserName;Password=UserPassword;

This usage type may cause vulnerabilities in our application thus using windows authentication can provide more
security in our applications. The following connection string will connect the database using windows
authentication.

Server=ServerName;Database=DatabaseName;Trusted_Connection=True;

With help of the following C# code, this is how we can see the usage of a connection string in an application.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlClient;

namespace TestConnectionString

{

class Program

{

static void Main(string[] args)

{

using (SqlConnection conn = new SqlConnection(«Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass;»))

{

conn.Open();

Console.WriteLine(«Connection is just opened»);

System.Threading.Thread.Sleep(10000);

conn.Close();

}

We can monitor this connection details to use dm_exec_sessions view in SQL Server.

SELECT

session_id

,program_name,

last_request_start_time,

last_request_end_time,

language,

date_format ,(select name from sys.databases where database_id =11) as ConnectedDatabase

FROM sys.dm_exec_sessions

where login_name =‘SuperHero’

and is_user_process=1

How to change language in SQL connection strings

SQL Server allows changing the language setting for the sessions. So, this option will change the system messages
and datetime formats in the session. We can change the language option in the connection string using the Language
attribute. In the following SQL connection string, we will change the language default language with Polish.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass;Language=Polish;

How to change application name in SQL connection string

Application Name helps to easily identify which application is connecting to SQL Server otherwise it can take more
effort to identify owners of the applications. We can change the Application Name to give a name in the connection
string.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application Name=SuperApp

How to change client workstation name in SQL connection strings

In a connection string, we can change the connecting machine name to use the Workstation ID attribute. In the following connection string, we will set the machine name as SuperNova.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application
Name=SuperApp;Workstation ID=SuperNova

SQL connection string and connection pooling

Generating a new connection is a bit resource-intensive task for the SQL engine. Therefore, SQL Server uses the connection pooling mechanism to get rid of this laborious task. The default setting of the connection pool is true,
therefore, we don’t need to change anything to enable this option. However, we can explicitly set this option in the connection string.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application
Name=SuperApp;Workstation ID=SuperNova;Pooling=true

The connection pool can be likened to a connection cache because in this working concept SQL SQL Server keeps ready to use in a pool instead of destroying the connections that their tasks have finished up. When a user requests a new connection with a matching connection string, the pooler looks for an available connection in the pool that corresponds to that connection string. If the pool worker finds a connection according to matching criteria it returns this ready connection to this request. When the application sends a signal to close the connection, instead of closing the connection, the pool worker sends it to the connection pool. In the C# code, we will
request 7 new connections and execute a very simple query.

When we run this console application it will generate 2 connection pools. The first 3 connections will place in a connection pool and the second 2 connections will place another pool.

This case can observe using performance counters of the .NET Framework Data Provider for SQL Server in PerfMon. The parameter NumberOfActiveConnectionPools shows the number of connection pools and NumberOfPooledConnections shows how
many connections are managed in these pools.

The reason for the generation of two separate connection pools is the application name difference in the connection
strings. Connections string dissimilarities cause to generate separate connection pools. Such as different database
names, authentication types, and other differences cause to generate different connection pools. Connection pools
are not destroyed until the active process ends or the connection lifetime is exceeded. We can set connection
lifetime in the connection string and it determines how long a connection will be kept in the connection pool.

Server=localhost;Database=SQLShackDemo;User Id=SuperHero;Password=1pass; Application
Name=SuperApp;Workstation ID=SuperNova;Pooling=true; Connection Lifetime=100

In our sample application, we have a sixth and seventh connection at the end of the code, their connection request will be given from the connection pool. In order to monitor all these complex processes, we can use an extended event. We can capture the login/logout events and we also will capture the rpc_completed event. After starting the application the extended event screen will be captured as below:

As we stated the first 5 connection creates a new connection and because of the different application names, SQL server creates two connection pool. When the sixth connection requests a connection, this connection is given from the first connection pool because their connection strings are similar. Also, this case is valid for the seventh connection. The seventh connection is given from the second connection pool. If the is_cached value indicates true, it means that the connection is given from the connection pool and this value seems to be correct for the sixth and seventh connections. We are seeing that the sp_reset_connection procedure is executed after the logout event. This procedure is called between logout and login event because it resets the state of the connection.

Conclusion

In this article, we have explored some details about the SQL connection string. As we have learned, different
connection string settings can change the connection behaviors.

Author
Recent Posts

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

Источник

Below is a cheat sheet for creating SQL Server client connection strings and finding them in common configuration files.

CREATING CONNECTION STRINGS

Authentication Options

Current Windows Account

Server=ServerInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Provided Windows Account

Server=ServerInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1;uid=DomainAccount;pwd=Password;"

Provided SQL Login

Server=ServerInstance;Database=Master;Connection Timeout=1;User ID=Username;Password=Password;"

Connection Type Options

TCP/IP

Server=TCP:ServerInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Named Pipes

Connecting to instances by name, forcing a named pipes connection.

Server=np:Server;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Server=np:ServerInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Default instance: Server=\APPHOSTpipeunitapp;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Named instance: Server=\APPHOSTpipeMSSQL$SQLEXPRESSSQLquery;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

VIA

Server=via:ServerInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Shared Memory

Server=lpc:ServernameInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Server=(local);Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Server=(.);Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Dedicated Admin Connection

Server=DAC:ServerInstance;Database=Master;Integrated Security=SSPI;Connection Timeout=1"

Other Options

Spoof Application Client

Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True;Application Name="My Application"

Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True;ApplicationName=".Net SqlClient Data Provider"

Note: Determine app name in sql server: select APP_NAME()

Set Encryption

Driver='ODBC Driver 11 for SQL Server';Server=ServerNameHere;Encrypt=YES;TrustServerCertificate=YES

Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True;Application Name="My Application";Encrypt=Yes

Encrypt Flag Notes:
Data sent between client and server is encrypted using SSL. The name (or IP address) in a Subject Common Name (CN) or
Subject Alternative Name (SAN) in a SQL Server SSL certificate should exactly match the server name (or IP address)
specified in the connection string.

Set Packet Size

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.packetsize(v=vs.110).aspx

Note: This could potentially be used to obfuscate malicious payloads from network IDS going over unencrypted connections.

"Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Packet Size=512"

FINDING CONNECTION STRINGS

ODBC/DNS Notes

https://technet.microsoft.com/en-us/library/hh771015.aspx

https://technet.microsoft.com/en-us/library/hh771014.aspx

Get all install ODBC drivers

Get-OdbcDriver

Get all install ODBC drivers for SQL Server that are 64 bit

Get-OdbcDriver -Name "SQL Server*" -Platform "64-bit"

Get all ODBC User DSNs for specified driver

$DsnArray = Get-OdbcDsn -DriverName "SQL Server*"

Get ODBC System DSNs by name

Get-OdbcDsn -Name "MyPayroll" -DsnType "System" -Platform "32-bit"

Get ODBC DSNs with names that contain a string

Get-OdbcDsn -Name "*Payroll*"

Universal Data Link (UDL) Files

https://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx

.UDL files often contain connection strings in a format similar to:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=servername;Initial Catalog=Northwind;Integrated Security=SSPI

Finding UDL files

c:

cd

dir /s /b *.udl

Get-ChildItem -Path C: -Filter *.udl -Recurse | select fullname

ApplicationHost.config Files

https://blog.netspi.com/decrypting-iis-passwords-to-break-out-of-the-dmz-part-2/

Decrypt Entire Config File

List application pools.

appcmd list apppools

appcmd list apppools /text:MyTestPool
Get clearext configuration file for specific pool.

appcmd list apppool "MyTestPool" /text:*

Decrypt Virtual Directory and Application Credentials in Config File

List virtual directories.

appcmd list vdir
List configuration content.

appcmd list vdir "Bike Shop/" /text:*

Web.config Files

https://blog.netspi.com/decrypting-iis-passwords-to-break-out-of-the-dmz-part-1/#2

Finding web.config files

c:

cd

dir /s /b web.config

Get-ChildItem -Path C: -Filter web.config -Recurse | select fullname

Finding registered web.config files via appcmd.exe

Common Paths:

C:Program FilesIIS Expressappcmd.exe
C:Program Files (x86)IIS Expressappcmd.exe
%windir%system32inetsrvappcmd

Common Commands:

%windir%system32inetsrvappcmd list vdir

dir /s /b | find /I "web.config"

Decrypted Web.config with aspnet_regiis.exe

C:WindowsMicrosoft.NETFrameworkv2.0.50727aspnet_regiis.exe -pdf "connectionStrings" c:MyTestSite

.dtsx Files

https://docs.microsoft.com/en-us/sql/integration-services/ssis-package-format?view=sql-server-2014

dir /s /b | find /I "*.dtsx*"

.bacpac Files

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/

Finding Connection Strings in .bacpac Files

One of the SQL Server backup file formats used with Azure is .bapac. Traditionally, .bacpac files are viewed through SQL Server Management Studio. However, they can also be read like a standard .zip file if the extension is changed to .zip. They often contain cleartext SQL Server credentials in the model.xml file.

dir /s /b *.bacpac

copy file.bacpac file.zip

powershell -c 'Expand-Archive -Path c:tempfile.zip -DestinationPath c:temp'

type c:tempmodel.xml | findstr "sqluser"

type c:tempmodel.xml| findstr "password"

type c:tempmodel.xml | findstr "authenticationtype"

Online References

https://support.microsoft.com/en-us/topic/what-is-a-dsn-data-source-name-ae9a0c76-22fc-8a30-606e-2436fe26e89f
https://msdn.microsoft.com/en-us/library/ms130822.aspx
https://msdn.microsoft.com/en-us/library/ms188642.aspx
https://technet.microsoft.com/en-us/library/ms191260(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms187662(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms189307(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms189595(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx
https://msdn.microsoft.com/en-us/library/hh568455(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.applicationname(v=vs.110).aspx
https://www.connectionstrings.com/sql-server/
https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/
https://azure.microsoft.com/en-us/blog/windows-azure-web-sites-how-application-strings-and-connection-strings-work/
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

Источник

Remove From My Forums

Question
User127454901 posted

Hello,

I need to create a connection to SQL Server with windows authentification in my code behind.

So I wrote that :

string conn = @»data source=sharepointAPSSQL;initial catalog=StrategicDWH;Integrated Security=SSPI;»;

The windows authentication works when I go directly in SQL Server Managment.

The weird things is that the error I get is : Login failed for user ‘BMCSSIPC-FHA$’. But this is not the login it must use for windows authentication (in fact this login seems to be my computer name with the adding of
the ‘$’).

Thanks for your help

Answers

User127454901 posted

OK, finally I have cut my site in two part ( so two web.config : one with windows authentication, the second with form authentication) and it answers to my needs.

Thks
- Marked as answer by
  
  Thursday, October 7, 2021 12:00 AM

Источник

SQL Server can be accessed using several technologies, each of which has different connection string particulars. Connection strings are provider/driver specific so one first needs to decide on a client API before formulating the proper string can be created.

All connection strings share the same basic format, name/value pairs separated by semicolons, but the actual connection string keywords may vary by provider. Which keywords are required or optional also vary by provider and providers often share the same keywords (or provide synonyms) to minimize the connection string changes when switching between different providers. Most connection string keywords are optional and need to be specified only when the default is not appropriate. Connection string values should be enclosed in single or double quotes when the value may include a semicolon or equal sign (e.g. Password=»a&==b=;1@23″)

The purpose of a connection string is to supply a SQL Server provider/driver with the information needed to establish a connection to a SQL Server instance and may also be used to specify other configuration values, such as whether connection pooling is used. At the end of the day, the provider/driver needs to know at least:

One typically uses the .Net Framework Provider for SQL Server (abbreviated to SqlClient here) in managed code and a SQL Server OLE DB provider or ODBC driver from unmanaged code. It is possible to use OLE DB or ODBC for SQL Server data access in managed code but there is seldom a reason to do so since SqlClient offers high-performance access to SQL Server natively.

The authoritative reference for SqlClient connection strings is http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx. My goal is not to rehash all of the keywords or illustrate the many combinations here but rather show the ones most commonly used along with best practices. I use the primary keywords rather than synonyms or equivalent keywords in the examples.

The SqlConnectionStringBuilder class provides a programmatic way to build connection strings needed by SqlConnection class. The nice thing about SqlConnectionStringBuilder is that it provides IntelliSense and avoids connection string typos. It should always be used when constructing connection strings based in user input (e.g. user id and password prompt). But you still need to know which connection string properties (keywords) you need to set along with the default values. The examples here apply regardless of whether or not you use yjr SqlConnectionStringBuilder class.

SqlClient Connection String Keyword Examples

Unlike other providers, there is no “Provider” or “Driver” connection string keyword in a SqlClient connection string. The .Net Framework Provider for SQL Server is implicit with a SqlConnection class so it is redundant to also specify the provider.

I’ll start with the minimal keyword(s) needed. The minimal SqlClient connection string need only specify the authentication method. The example below specifies Windows authentication using “Integrated Security=SSPI”. This connection string will connect the default instance on the local machine using Windows authentication under the current process Windows security credentials.

Listing 1: Connect to local default instance using Windows authentication

To connect to the local default instance using SQL authentication, just specify the credentials using the “User ID” and “Password” keywords instead of “Integrated Security=SSPI” keyword. SQL authentication is the default when “Integrated Security” or “Trused_Connection” keyword is not specified. Although I commonly see «Persist Security Info=False» also specified (a best practice from a security perspective), that is the default setting and may be omitted. Be aware that you should encrypt connection strings (or passwords in general) stored in configuration files when using SQL authentication.

User ID=MyLogin;Password=MiP@ssw0rd

Listing 2: Connect to local default instance using SQL authentication

One often connects to a remote SQL Server. Along with the authentication method, add the Data Source keyword to specify the desired SQL Server name or network address.

Data Source=SQLSERVERNAME;Integrated Security=SSPI

Listing 3: Connect to default instance on host SQLSERVERNAME using Windows authentication

Data Source=SQLSERVERNAME;User ID=MyLogin;Password=MiP@ssw0rd

Listing 4: Connect to instance on host SQLSERVERNAME using SQL authentication

Note that these same connection strings may be used to connect locally or remotely. Personally, I recommend always specifying the Data Source even when connecting locally. This makes it easy to move the application to another machine using with the same configuration and helps avoid oversights.

It is usually best to let SqlClient determine the appropriate network library to use rather than an explicit specification. SqlClient will figure out the appropriate network library based on the specified Data Source value. When you connect to a local instance using an unqualified name (or the value “(local)”), Shared Memory is used by default. SqlClient will use TCP/IP if a FQDN (e.g. SQLSERVERNAME.MyDOMAIN.COM) or IP address is specified regardless of whether the instance is local or remote. Since TCP/IP is most commonly used nowadays, I’ll focus on TCP/IP in this article and use a FQDN in the subsequent examples to avoid ambiguity.

It is often desirable to specify the initial database context in the connection sting. If omitted, the default database of the authenticated account is used. This is accomplished using either the “Initial Catalog” or “Database” keyword. I suggest always including the “Initial Catalog” keyword.

Data Source=SQLSERVERNAME.MYDOMAIN.COM;Integrated Security=SSPI;Initial Catalog=MyDatabase

Listing 4: Connect to default instance on host SQLSERVERNAME using Windows authentication with initial database context of MyDatabase

Named Instances

The connection strings I’ve shown so far assume the target is a default SQL Server instance listening on port 1433. One can run multiple instances of SQL Server on the same host using the named instance feature. If your target database instance is a named instance, SqlClient will also need to know the instance name or instance port number. The instance name can be specified by appending a backslash and instance name to the Data Source value:

Data Source=SQLSERVERNAME.MYDOMAIN.COMMYINSTANCE;Integrated Security=SSPI;Initial Catalog=MyDatabase

Listing 5: Connect to named instance on host SQLSERVERNAME using Windows authentication with initial database context of MyDatabase

As an aside, I often see connectivity problems with named instances due to oversights in the SQL Server configuration. When an instance name is specified, SqlClient interrogates the SQL Server Brower service on the SQL Server host to determine the instance port (or named pipe name). The SQL Server Brower service is disabled by default so you need to enable and start it in order to connect by the instance name. This can be done using the SQL Server Configuration Manager tool. Also, since the SQL Server Brower service communicates over UDP port 1434, that port must be allowed through firewalls.

You can specify a port number instead of instance name to directly to a named instance (or to a default instance listing on a non-standard port). The port may be specified by appending a comma and port number to the data source value. The needed port number can be ascertained from the SQL Server Configuration Manager tool.

Data Source=SQLSERVERNAME.MYDOMAIN.COM,60086;Integrated Security=SSPI;Initial Catalog=MyDatabase

Listing 6: Connect to instance on host SQLSERVERNAME listening on port 60086 using Windows authentication with initial database context of MyDatabase

Additional Keywords

In addition to the “Data Source”, “Initial Catalog” and “Integrated Security” (or “User Id” and “Password”) keywords I’ve discussed so far, I recommend that “Application Name” also be specified. The specified string is helps identify the application when monitoring activity on the database server. This is especially useful when an application server or client hosts multiple applications.

Data Source=SQLSERVERNAME.MYDOMAIN.COM;Integrated Security=SSPI;Initial Catalog=MyDatabase;Application Name=Connection String Example

Listing 7: Connect to default instance on host SQLSERVERNAME using Windows authentication with initial database context of MyDatabase with application name specification

In my opinion, the many other keywords are noise unless the default values are inappropriate for your environment.

Summary

You can get by nicely in most cases with only the 4 or 5 SqlClient connection string keywords I’ve discussed here. I suggest you establish a connection string standard that includes the “Data Source”, “Initial Catalog”, “Application Name” keywords plus the authentication method, “Integrated Security=SSPI” or “User Id” and “Password”.

Источник