SQL Server Problems

1. sql2005 prompts "This user is not associated with a trusted SQL server connection" SOLUTION

Problem: an error is reported when you connect to the database engine using the "SQL Server Identity Authentication" of a SA user or user: "This user is not associated with a trusted SQL server connection." solution: 1. Right-click the database instance and select "properties". 2. In the displayed "server properties" window, select "security "; 3. In Server Authentication mode, select "SQL Server and Windows Authentication Mode" 4. Restart the database. Author: sqlserver 2. database connection failureServer is equivalent to localhost, 127.0.0.1, and (local). However, you only need to replace it with the name of the server that SQL server logs on, such as ae5ec3e392d24e2 \ sqlexpress3.. Net SQL server connection string syntax.

Database Connectivity has developed into a standard aspect of application development. Database connection strings are now a standard prerequisite for each project. I found that in order to find the required syntax, I often need to copy the connection string from another application or perform a search. This is especially true when interacting with SQL Server because it has too many connection string options. Now let's take a look at the many aspects of the connection string.

Connection string

During Object Instantiation or establishment, database connection strings are passed to necessary objects through attributes or methods. The format of the connection string is a list of key/value parameter pairs divided by semicolons. List A contains an example in C # That describes how to connect to SQL Server by creating a sqlconnection object (the actual connection string is allocated through the connectionstring attribute of the object ). List B contains the VB. NET version.

Reference content:

String cstring = "Data Source = server; initial catalog = dB; user id = test; Password = test ;";

Sqlconnectionconn = new sqlconnection ();

Conn. connectionstring = cstring;

Conn. open ();

Reference content:

Dim cstring as string

Cstring = "Data Source = server; initial catalog = dB; user id = test; Password = test ;"

Dim conn as sqlconnection = new sqlconnection ()

Conn. connectionstring = cstring

Conn. open ()

The connection string specifies the database server and database, and the username and password required to access the database. However, this format does not apply to all database interactions. It does have many available options, many of which have synonyms. With data source, initial catalog, user ID, password, and other elements, the following options are available:

* Application name: the name of the application. If it is not specified, its value is. Net sqlclient data provider ).

* Attachdbfilename/extended properties/initial file name: name of the main file that can be connected 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) for a connection to the server to wait before termination. The default value is 15.

* Connection lifetime: when a connection is returned to the connection pool, its creation time is compared with the current time. If the time span exceeds the validity period of the connection, the connection will be canceled. The default value is 0.

* Connection Reset: Indicates whether a connection is reset when it is removed from the connection pool. A pseudo-valid server does not need to run back and forth after obtaining a connection. The default value is true.

* Current language: the name of the SQL Server language record.

* Data Source/Server/address/ADDR/Network Address: The name or network address of the SQL server instance.

* Encrypt (encrypted): When the value is true, if an authorization certificate is installed on the server, SQL server uses SSL encryption for all data transmitted between the client and the server. The accepted values include true, false, Yes, and no ).

* Enlist: indicates whether the connection pool program automatically registers the connection in the current transaction context of the Creation thread. The default value is true.

* Database (database)/initial catalog (initial cataloguing): name of the database.

[B] * Integrated Security (Integrated Security)/trusted connection (trusted connection): Indicates whether Windows authentication is used to connect to the database. It can be set to "true", "false", or "true-to-peer" sspi. Its default value is "false.

* Max pool size (maximum capacity of the Connection Pool): the maximum number of connections allowed by the connection pool. The default value is 100.

* Min pool size (minimum capacity of the Connection Pool): the minimum number of connections allowed by the connection pool. The default value is 0.

* Network library/NET: The network library used to establish a connection to an SQL server instance. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol/RPC), dbmsvinn (Banyan Vines), dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP ). The dynamic Connection Library of the Protocol must be installed to an appropriate connection. The default value is TCP/IP.

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

* Password/pwd: Password corresponding to the account name.

* Persist Security info: used to determine whether security information is available after a connection is established. If the value is true, sensitive data such as the user name and password is available, and false data is unavailable. Resetting the connection string will reconfigure the values of all connection strings, including passwords. The default value is false.

* Pooling: determines whether to use the connection pool. If the value is true, the connection will be obtained from the appropriate connection pool, or, if necessary, the connection will be created and then added to the appropriate connection pool. The default value is true.

* User ID: the account name used to log on to the database.

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

The following connection string uses a trusted connection and the specified Login certificate (which is less secure than the administrator password) to establish a connection with the northwind database on the test \ dev1 Server:

Reference content:

Server = test \ dev1; database = northwind; user id = sa;

Password =; trusted_connection = true;

The next connection string uses the TCIP/IP address and a specified IP Address:

Reference content:

Data Source = 192.162.1.100, 1433; network library = dbmssocn;

Initial catalog = northwind; user id = sa; Password =;

The options used can be easily included in the connection string, but they still depend on your application and its requirements. Knowing what is available is very beneficial so that you can use it properly.

Use ADO. NET 2.0

Ado. NET 2.0 introduces a new connection string Generator for each. NET Framework data provider. Keywords are listed as attributes so that the connection string syntax takes effect before being submitted to the data source. There are also new classes that make it easy to store and retrieve connection strings in the configuration file and encrypt them in a protected way.

More or less, exactly what you need.

Connecting to SQL Server through a. NET application provides multiple options for specific connection parameters and other options. It can be as simple as specifying database, server, and login creden。, or as complicated as setting buffer connection pools and security options. Ado. NET 2.0 makes the connection string option more flexible through the database class. You only need to use the options required by the application and ignore other options. In addition, if you find yourself in trouble and cannot remember to connect string syntaxes, you can refer to the following URL connectionstrings.com