C # ways to connect databases and sql2005 remote connections _mssql

One: C # connect to SQL database

Copy Code code as follows:

Data source=myserveraddress;initial catalog=mydatabase; User Id=myusername; Password=mypassword;
Data source=190.190.200.100,1433; Network Library=dbmssocn;initial catalog=mydatabase; User Id=myusername; Password=mypassword;
Server=myserveraddress;database=mydatabase; User Id=myusername; Password=mypassword; Trusted_connection=false;
Server=myserveraddress;database=mydatabase; Trusted_connection=true;
Server=myservername/theinstancename;database=mydatabase; Trusted_connection=true;
Data source=myserveraddress;initial catalog=mydatabase;integrated Security=sspi;

1:integrated Security Parameters

When the integrated security is set to True, the UserID in front of the connection statement, PW, does not work, that is, the Windows Authentication mode is used.
Only set to False or omit the item, and then follow UserID, PW to connect.
Integrated security can also be set to: SSPI, which is the equivalent of true, which is recommended instead of true.

Copy Code code as follows:

Data source=myserveraddress;initial catalog=mydatabase;integrated Security=sspi;
Data source=myserveraddress;initial catalog=mydatabase;integrated security=true;
Data source=myserveraddress;initial catalog=mydatabase;; User Id=myusername; Password=mypasswordintegrated Security=false;

2: Parameter Trusted_Connection

Trusted_connection=true, authentication is performed using the current Windows account credentials
Trusted_connection=false will not take a trusted connection (that is, not Windows authentication), instead of the SQL Server 2000 authentication method
Server=myserveraddress;database=mydatabase; User Id=myusername; Password=mypassword; Trusted_connection=false;
Server=myserveraddress;database=mydatabase; Trusted_connection=true;

3:initial Catalog is the name of the database you want to connect to.

4:wince Connection
Data source=myserveraddress;initial catalog=mydatabase;integrated Security=sspi; User Id=mydomain/myusername; Password=mypassword;

Two: You can use sqlconnectionstringbuilder so that you don't have to remember the name.

Copy Code code as follows:

SqlConnectionStringBuilder SCSB = new SqlConnectionStringBuilder ();
SCSB. DataSource = @ "(local)/sqlexpress";
SCSB. IntegratedSecurity = true;
SCSB. InitialCatalog = "Northwind";
SqlConnection myconnection = new SqlConnection (SCSB. ConnectionString);

Three: You can use the setting from the property to set the connection string

1: Select in type (connection string),
2: Select the data source in Datasouce, and then enter the server name in server, locally (local)/sqlexpress
3: Choose login Authentication Mode, this time select Windows authentication (that is, trust connection integrated security=true)
4: Select the database name, confirm can
Data source= (local)/sqlexpress;initial catalog=northwind;integrated security=true
Server =./sqlexpress;integrated Security = True;database = Northwind

Four: SQL2005 remote server connection method

How to open SQL Server 2005 's 1433 port:
Configuration Tool->sql Server Configuration Manager->mssqlserver protocol to see if the TCP/IP protocol is started, if it starts, right-click menu Point "Properties", select "IP Address" in the paging menu, "IP1" and " IP2 "TCP port" is 1433, "enabled" is changed to "yes"
Configuration Tool->sql Server Configuration manager->sql Native Client Configuration-> Clients protocol->TCP/IP Select "Properties" in the TCP/IP right-click menu to confirm the "default port" Is 1433, "enabled" is "yes".

SQL Server 2005 Remote connections Configure TCP/IP properties:
Surface area Configuration--> Database Engine--> Remote Connections--->using tcp/it SQL Server perimeter application Configurator? Services and Connectivity Perimeter Configure the database Englie remote connection? enabled (TCP/IP and named pipes for remote connections)
SQL Server Configuration Manager? SQL2005 network configuration? Enable TCP/IP and Named pipes

Other notes See below: sqlserver2005 (Express version), in order to facilitate management, you also need to go to the next manage manager:
After installing the Manage manager, connect the sqlserver2005 in the program, the following points are to be noted.
1. Open the Sql2005 remote connection function, the opening method is as follows, the Configuration tool->sql server perimeter application Configurator-> service and the connection's peripheral application Configurator-> opens the database MSSQLServer node under the Engine node, first selects " Remote connection, and then select "Use both TCP/IP and Named pipes", and then restart the database service when you are sure.
2. The login settings are changed to, SQL Server and Windows authentication are selected at the same time, as follows: Manage Manager->windows authentication (first time in Windows),- > Object Explorer Select your data server-the right key > Properties >security>sql Server and Windows authentication are selected at the same time.
3: Set a SQL Server way username and password, set as follows: Manage manager->windows authentication>new query>sp_password null, ' sa123456 ', ' Sa ' so set a user named SA, Password: sa123456 users, the next time you log in, you can use SQL Server, user name SA, password: sa123456 users into the database.
4: After the above three steps, so write the connection string can be successfully entered the database, (server=./sqlexpress;uid=sa;pwd=sa123456;database=master ";

Five: SQL2000 remote server connection method

1: See ping server IP can ping pass.
2: Enter the Telnet server IP port under DOS or command line to see if it is connected. For example, Telnet 202.114.100.100 1433 typically has a port value of 1433 because 1433 is SQL Server 2000 's default listening port for TCP/IP. If there is a problem, usually this step will go wrong. The usual hint is "... Unable to open connection, connection failed.
If there is a problem with this step, you should check the following options.
1 Check to see if the remote server has started the SQL Server 2000 service. If not, start.
2 Check that the TCP/IP protocol is not enabled on the server side, because remote connections (via the Internet) require this protocol. The check method is to open the Start menu on the server-> program-> Microsoft SQL server-> Server Network Utility to see if there is a TCP/IP protocol in the enabled Protocol, and if not, enable it.
3 Check that the TCP/IP port for the server is configured with port 1433. 　　Still in the Server Network Utility, view the properties of the TCP/IP enabled in the protocol, ensure that the default port is 1433, and that the Hide Server checkbox is not checked. In fact, if the default port is modified, it is OK, but when the client does a Telnet test, the port number of the write server must be the same as the server-configured port number. If the Hidden server checkbox is checked, it means that the client cannot see the server by enumerating the servers, and that it protects, but does not affect the connection, but the default port for the TCP/IP protocol is implicitly modified to 2433 and must be changed when the client connects.
4 If the server-side operating system has been SP2 patch, the Windows Firewall must be configured, to open 1433 ports to it, usually in the test can be directly turned off Windows Firewall (the other firewall also turned off the best).
5 Check to see if the server is listening on port 1433. If the server is not listening on the 1433 port of the TCP connection, it is not connected. The check method is to enter Netstat-a-N or Netstat-an below the server's DOS or command line, and see if there are any items similar to the TCP 127.0.0.1 1433 listening in the results list. If not, you typically need to have at least SP3 patches on SQL Server 2000.　　In fact, start Query Analyzer on the server side, enter the SELECT @ @version can see the version number, version number under 8.0.2039 needs to be patched. If the above is all right, then you do telnet server IP 1433 test, you will see the screen after a flash of the cursor in the upper left corner flashing. Congratulations, you can start the connection in Enterprise Manager or Query Analyzer immediately.

3: Check the client setup program-> Microsoft SQL Server-> Client Network usage tool. As in the server Network Utility, make sure that the client-side TCP/IP protocol is enabled and that the default port is 1433 (or another port, consistent with the server side).
4: In Enterprise Manager or query that Analyzer connection Test Enterprise Manager-> right key SQL Server group-> new SQL Server registration-> next-> write remote ip-> next-> Select SQL Server login-> Next-> Write login name and password (sa,password)-> next-> next-> complete Query Analyzer-> file-> connection-> write remote ip-> write login and password (sa,password)-& Gt　　Determining is usually recommended in Query Analyzer because by default the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, and the Query Analyzer is 15 seconds. To modify the default connection timeout: Enterprise Manager-> tools-> option-> in the SQL Server Enterprise Manager Properties window that pops up, click the Advanced tab-> connection settings-> Enter a larger number query in the box after the login timeout (seconds) Analyzer-> tool-> option-> Connection-> Enter a larger number in the box after the login timeout (in seconds) that is usually connected, and if prompted incorrectly, go to the next step.
5: The error is usually caused by SQL Server using Windows only authentication, so users cannot connect using SQL Server's login account, such as SA. The workaround looks like this:
1 Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server.
2 Expand SQL Server Group, right-click the name of the SQL Server server, select Properties, and select the Security tab.
3 under Authentication, select SQL Server and Windows.
4 Restart the SQL Server service. (net stop MSSQLSERVER stops service at DOS or command line, net start MSSQLServer service is also a quick way).