Database connection String Reprint

"Summary: How to use the database connection string" Summary: How to use the database connection string

1. Review

What is a database connection string?

The format of the connection string is a semicolon-delimited list of key/value parameter pairs. The connection string contains a collection of property name/value pairs. Each attribute/value pair is separated by a semicolon.

Propertyname1= "Value1"; Propertyname2= "Value2"; Propertyname3= "Value3";

Similarly, the connection string must contain the SQL Server instance name: Data source=servername;

In general, the connection string for a database is divided into two types: Windows authentication and SQL Server authentication. Each method has its own pros and cons. But the basic rules are the same, and the basic rules are as follows:

authentication mode, data Source< Span style= "font-family: the song Body;" > (data source) initial Catalog (initial catalog) user idpassword (password

? · Application name(application name): The name of the application. If not specified, it has a value of . NET SqlClient data Provider.

? · AttachDbFileName/Extended Properties(extended attribute)/Initial file name(initial file name): The name of the primary file that can connect to the database, including the full path name. The database name must be specified with the keyword database.

? · Connect Timeout (connection timeout)/Connection timeout(connection timeout): The length of time (in seconds) that a connection to the server waits before terminating, with the default value of .

? · Connection Lifetime(Connection Life time): When a connection is returned to the connection pool, its creation time is compared to the current time. If the time span exceeds the validity of the connection, the connection is canceled. Its default value is 0.

? · Connection Reset(Connection reset): Indicates whether a connection is reset when it is removed from the connection pool. A pseudo-effective when a connection is obtained there is no need to perform an additional server back and forth, its default value is true.

? · Current Language(currently language): The name of the SQL Server language record.

data source (data source)/< Span style= "Font-family:tahoma;" >serveraddress addrnetwork address (network address): sql server

encrypt (encrypted): When the value is true, if the server has an authorization certificate installed, sql serverssl encryption. The accepted values are true (True), false (pseudo), no (NO).

? · Enlist(registration): Indicates whether the connection pooling program automatically enlists the connection in the context of the current transaction in which the thread was created, with a default value of True.

? · database/Initial catalog(initial Catalog): The name of the database.

? · Integrated Security(Integrated Secure)/Trusted Connection(trusted connection): indicates whether Windows authentication is used to connect to the database. It can be set to a true, pseudo, or an SSPI with true equivalence , and its default value is pseudo.

? · Max Pool size(maximum connection pool capacity): The maximum number of connections allowed for a connection pool, with a default value of.

? · min Pool size(the minimum size of the connection pool): The minimum number of connections allowed for the connection pool, with a default value of 0.

?·Network Library(Network library)/Net(Network):Used to build into aSQL Server DBNMPNTW (Named Pipes) DBMSRPCN (Multiprotocol/RPC) dbmsvinn (Banyan Vines) DBMSSPXN (Ipx/spx) and dbmssocn (Tcpip) tcp/ip

? · Packet size(packet size): The amount of network packets used to communicate with the database. The default value is 8192.

? · Password(password)/Pwd: The password that corresponds to the account name.

? · Persist Security Info(keep security information): used to determine if the safety information is available after the connection has been established. If the value is true, data that is sensitive to security, such as the user name and password, is available and is not available if the value is pseudo. Resetting the connection string will reconfigure the values of all connection strings, including passwords. Its default value is pseudo.

? · Pooling(pool): Determines whether connection pooling is used. If the value is true, the connection is obtained from the appropriate connection pool, or, if necessary, the connection is created and then added to the appropriate connection pool. Its default value is true.

? · User ID(username ): The name of the account used to log in to the database.

? · Workstation ID(workstation ID): The name of the workstation that is connected to SQL Server . The default value is the name of the local computer.

1. How Windows is authenticated

1. One of the easiest ways to get the most out of the way is to use it in the most unavoidable circumstances!
   First of all: anywhere on the computer to create a new suffix. udl file;
   
   Then: Double hit Open this file, will automatically pop up the Data Link Properties dialog box, as shown below, click OK after the configuration OK.
   
   Finally, drag the UDL file to Notepad and open it to see the connection string.
   

Each type of authentication has its advantages and disadvantages.

Windows authentication uses a single user repository source, so you do not need to configure users for database access separately. The connection string does not contain a user ID and password, thereby eliminating the risk of exposing the user ID and password to unauthorized users. Users and their roles can be managed in Active Directory without having to explicitly configure their properties in SQL Server.

The disadvantage of Windows authentication is that it requires customers to connect to SQL Server through a secure channel that is supported by the security subsystem of Windows. If application sequencing is required to connect through an unsecured network (such as the Internet), SQL Server,windows authentication will not work. In addition, this authentication method also partially transfers the responsibility of managing database access control from the DBA to the system administrator, which may be a problem in a determined environment.

In general, in order to use Windows authentication when designing a generic application, some aspects will be enhanced. Most companies ' databases reside on a more robust Windows server operating system that supports Windows authentication. The separation of the data access layer and the data presentation layer also facilitates the application of encapsulating the data access code in the middle-tier component idea, which typically runs in the internal network with the database server. When this is designed, there is no need to establish a database connection through an unsecured channel. In addition, Web services make it much less necessary to directly connect to databases in different domains.

Iii. How SQL Server is authenticated

SQL Server authentication Standard connection:

Data Source = myserveraddress; Initial Catalog = myDataBase; User Id = MyUserName; Password = mypassword;

server=.\sqlexpress; Database=testdb; Uid=sa; pwd=123456

with local SQL Server (localhost), if you want to run with a remote server, you should assign the correct server to the data Source property in the sample object. In addition, you must specify one of the two supported authentication methods (that is, Windows authentication and SQL Server Authentication). Windows authentication uses the Windows logon user to connect to the database, and SQL authentication requires that the SQL Server user ID and password be explicitly specified. To use Windows authentication, you must include the Integrated Security attribute in the connection string:

Data source=servername;integrated security=true;

By default, the Integrated Security property is False, which means that Windows authentication is disabled. If you do not explicitly set the value of this property to true, the connection will use SQL Server authentication, so you must provide the SQL Server user ID and password. Other values that the Integrated security attribute can recognize are only SSPI (Security support Provider Interface, secure Provider Interface). The value SSPI is supported on all Windows NT operating systems, including Windows NT 4.0, 2000, and XP. It is the only interface that can be used when using Windows Authentication, which is equivalent to setting the integrated Security property value to True.

When using SQL Server connection mode, take the local server (LocalHost), Database (TestDB) as an example, you can have some of the following connection methods

Data source=localhost;initial catalog= testdb;integrated Security=sspi; Persist Security Info=false; Workstation id=xxx; Packet size=4096;

Server=localhost; Persist Security info=false;integrated security=sspi;database= TestDB; Uid=sa; Pwd=sa;initial Catalog=northwind;data Source=localhost; Connect timeout=20;

In Windows Authentication mode, SQL Server uses the Windows security subsystem to validate user connections. SQL server does not check the user ID and password in the connection string, even if the user ID and password are specified by the display. Because only Windows NT, 2000, and XP support SSPI, if you are using these operating systems, you can only use Windows Integrated Security policy to connect to SQL Server. Regardless of which operating system you use, when you use SQL Server authentication, you must specify a user ID and password in the connection string:

server= Servername,data source=db; User Id=id; Password=pwd

Finally provide an authoritative string query website, English, very practical.
http://www.connectionstrings.com/

Database connection String Reprint