The common declarations in the connection string are:
The server declares Data Source, server, and addr.
Database declarations Initial catalog and databases.
Security statements for Integrated Windows accounts integrated and Trusted_Connection.
Use database account security to declare user IDs and password, and so on.
For access to the database account, usually we see in some resources ado.net string concatenation is often the following:
String connstr = "Server = localhost;
User ID = sa; Password = XXX; Database = Northwind ";
For an account that integrates Windows security, the connection string is typically written as follows:
String connstr = "Server = localhost;
Integrated Security = SSPI; Database = Northwind ";
or string connstr = "Server = localhost;
Trusted_Connection = yes; Database = Northwind ";
Using Windows Integrated Security authentication has many advantages in accessing the database: Higher security, faster access, less work to redesign the security architecture, hard coding of connection strings, etc., and is worth using.
SQL Native Client ODBC Driver
Standard secure connection
Driver={sql Native Client}; Server=myserveraddress;database=mydatabase; Uid=myusername; Pwd=mypassword;
Are you using SQL Server Express? Please use the connection expression "host name \SQLExpress" in the "Server" option.
A trusted connection
Driver={sql Native Client}; Server=myserveraddress;database=mydatabase; Trusted_connection=yes;
"Integrated SECURITY=SSPI" is the same as "Trusted_connection=yes".
Connect to an instance of SQL Server
The expression for the specified server instance is the same as the connection string for other SQL Server.
Driver={sql Native Client}; Server=myservername\theinstancename;database=mydatabase; Trusted_connection=yes;
Specify User name and password
Oconn.properties ("Prompt") = adPromptAlways
Driver={sql Native Client}; Server=myserveraddress;database=mydatabase;
Using Mars (multiple active result sets)
Driver={sql Native Client}; Server=myserveraddress;database=mydatabase; Trusted_connection=yes; Mars_connection=yes;
"Multipleactiveresultsets=true" and "Mars_connection=yes" are the same.
Use Ado.net 2.0 as a module for Mars. Mars does not support Ado.net 1.0 and Ado.net 1.1.
Validating network data
Driver={sql Native Client}; Server=myserveraddress;database=mydatabase; Trusted_connection=yes; Encrypt=yes;
Connect to a local SQL Server Express instance using the attached local database file
Driver={sql Native Client}; server=.\sqlexpress; Attachdbfilename=c:\asd\qwe\mydbfile.mdf; Database=dbname; Trusted_connection=yes;
Why do I use the database parameter? If a database with the same name is already attached, SQL Server will not be reattached.
Connect to a local SQL Server Express instance by using the database files in the Local Data folder
Driver={sql Native Client}; server=.\sqlexpress; attachdbfilename=| Datadirectory|mydbfile.mdf; Database=dbname; Trusted_connection=yes;
Why do I use the database parameter? If a database with the same name is already attached, SQL Server will not be reattached.
Database Mirroring
Data source=myserveraddress; Failover partner=mymirrorserver;initial catalog=mydatabase;integrated security=true;
SQL Native Client OLE DB Provider
Standard connection
PROVIDER=SQLNCLI; Server=myserveraddress;database=mydatabase; Uid=myusername; Pwd=mypassword;
Are you using SQL Server Express? Please use the connection expression "host name \SQLExpress" in the "Server" option.
A trusted connection
PROVIDER=SQLNCLI; Server=myserveraddress;database=mydatabase; Trusted_connection=yes;
"Integrated SECURITY=SSPI" is the same as "Trusted_connection=yes"
Connecting to an instance of SQL Server
The expression for the specified server instance is the same as the connection string for other SQL Server.
PROVIDER=SQLNCLI; Server=myservername\theinstancename;database=mydatabase; Trusted_connection=yes;
Use account number and password
Oconn.properties ("Prompt") = adPromptAlways
oConn.Open "PROVIDER=SQLNCLI; Server=myserveraddress;database=mydatabase;
Using Mars (multiple active result sets)
PROVIDER=SQLNCLI; Server=myserveraddress;database=mydatabase; Trusted_connection=yes; Marsconn=yes;
"Multipleactiveresultsets=true" and "Mars_connection=yes" are the same.
Use Ado.net 2.0 as a module for Mars. Mars does not support Ado.net 1.0 and Ado.net 1.1.
Validating network data
PROVIDER=SQLNCLI; Server=myserveraddress;database=mydatabase; Trusted_connection=yes; Encrypt=yes;
Connect to a local SQL Server Express instance using the attached local database file
PROVIDER=SQLNCLI; server=.\sqlexpress; Attachdbfilename=c:\asd\qwe\mydbfile.mdf; Database=dbname; Trusted_connection=yes;
Why do I use the database parameter? If a database with the same name is already attached, SQL Server will not be reattached.
Connect to a local SQL Server Express instance by using the database files in the Local Data folder
PROVIDER=SQLNCLI; server=.\sqlexpress; attachdbfilename=| Datadirectory|mydbfile.mdf; Database=dbname; Trusted_connection=yes;
Why do I use the database parameter? If a database with the same name is already attached, SQL Server will not be reattached.
Database Mirroring
Data source=myserveraddress; Failover partner=mymirrorserver;initial catalog=mydatabase;integrated security=true;
SqlConnection (. NET)
Standard connection
Data source=myserveraddress;initial catalog=mydatabase; User Id=myusername; Password=mypassword;
You can specify an instance of SQL Server by using ServerName\InstanceName as the data source.
Are you using SQL Server Express? Please use the connection expression "host name \SQLExpress" in the "Server" option.
Standard Security Alternative syntax
Server=myserveraddress;database=mydatabase; User Id=myusername; Password=mypassword; Trusted_connection=false;
Trusted connections
Data source=myserveraddress;initial catalog=mydatabase;integrated Security=sspi;
Trusted Connection Alternative syntax
Server=myserveraddress;database=mydatabase; Trusted_connection=true;
Connecting to an instance of SQL Server
The expression for the specified server instance is the same as the connection string for other SQL Server.
Server=myservername\theinstancename;database=mydatabase; Trusted_connection=true;
Secure connections from WinCE devices
Data source=myserveraddress;initial catalog=mydatabase;integrated Security=sspi; User Id=mydomain\myusername; Password=mypassword;
Can only be used for CE equipment.
A connection with an IP address
Data source=190.190.200.100,1433; Network Library=dbmssocn;initial catalog=mydatabase; User Id=myusername; Password=mypassword;
Using Mars (multiple active result sets)
Server=myserveraddress;database=mydatabase; Trusted_connection=true; Multipleactiveresultsets=true;
Use Ado.net 2.0 as a module for Mars. Mars does not support Ado.net 1.0 and Ado.net 1.1.
Connect to a local SQL Server Express instance using the attached local database file
server=.\sqlexpress; Attachdbfilename=c:\asd\qwe\mydbfile.mdf;database=dbname; Trusted_connection=yes;
Why do I use the database parameter? If a database with the same name is already attached, SQL Server will not be reattached.
Connect to a local SQL Server Express instance by using the database files in the Local Data folder
server=.\sqlexpress; attachdbfilename=| Datadirectory|mydbfile.mdf; Database=dbname; Trusted_connection=yes;
Why do I use the database parameter? If a database with the same name is already attached, SQL Server will not be reattached.
Use a user instance on a SQL Server Express instance
Data source=.\sqlexpress;integrated security=true; attachdbfilename=| Datadirectory|\mydb.mdf; User instance=true;
Database Mirroring
Data source=myserveraddress; Failover partner=mymirrorserver;initial catalog=mydatabase;integrated security=true;
Asynchronous processing
Server=myserveraddress;database=mydatabase;integrated security=true; asynchronous processing=true;
