1. C# Connect to SQL databases
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 parameter
When Integrated Security is set to True, the UserID and PW in front of the connection statement do not work, that is, the windows authentication mode is used.
Only when set to False or omitted will the connection be based on UserID, PW.
Integrated Security can also be set to: sspi, which is equivalent to True, and it is recommended to use this instead of True.
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 will be performed using the current Windows account credentials
Trusted_Connection=false; The trusted connection method (i.e., the Windows authentication method is not used) will be used 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;
2: You can use SqlConnectionStringBuilder so you don't have to remember the name.
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 in the properties to automatically set the connection string
1: Select (connection string) in type,
2: Select the data source in DataSouce, and then enter the server name in Server, using local\SQLExpress
3: Select the login verification method, this time select Windows verification (i.e., trust the connection Integrated Security=True)
4: Select the database name and confirm
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 port 1433 in SQL Server 2005:
Configuration Tool->Sql Server Configuration Manager->MSSQLSERVER protocol to see if the TCP/IP protocol is started, if it starts, right-click the menu "Properties", select "IP Address" in the pagination menu, and change "TCP Port" to 1433 in "IP1" and "IP2", and "Enabled" to "Yes"
Configuration Tool->Sql Server Configuration Manager->SQL Native Client Configuration->Client Protocol->TCP/IP Select Properties in the TCP/IP right-click menu and confirm that the Default Port is 1433 and the Enabled is Yes.
SQL Server 2005 Remote Connection Configuration TCP/IP Properties:
Surface Area Configuration --> Database Engine --> Remote Connections --->Using TCP/IT SQL Server Peripheral App Configurator? Service and connectivity peripheral configuration? Remote connection? Enabled (TCP/IP and named pipes for remote connections)
SQL Server Configuration Manager? SQL2005 network configuration? Enable TCP/IP and named pipes
For other instructions, see sqlserver2005 (Express version), for ease of management, you also need to go to the next manage manager:
After installing the manage manager, connect sqlserver2005 in the program, the following points should be noted.
1. Enable the sql2005 remote connection function, the opening method is as follows, configuration tool - >sql server peripheral application configurator - > service and connected peripheral application configurator - > Open the Database Engine node under the MSSQLSERVER node, first select "Remote Connection", and then select "Use TCP/IP and named pipes at the same time", after confirming, restart the database service.
2. The login settings are changed to Sql server and windows Authentication are selected at the same time, the specific settings are as follows: manage manager - >windows authentication (the first time to enter with windows), - select your data server in > object explorer - right-click > properties>security>Sql server and windows Authentication method is also selected.
3: Set a Sql server username and password, the specific settings are as follows: managemanager->windows Authentication>new query>sp_password null, 'sa123456', 'sa' So set a user named sa and password is: sa123456, the next time you log in, you can use the Sql server method, the user name is sa, The user with the password is: sa123456 has entered the database.
4: After completing the above three steps, write the connection string so that it can enter the database smoothly.
(server=.\sqlexpress; uid=sa; pwd=sa123456; database=master";
five: SQL2000 remote server connection method
1: Check whether the ping server IP can be pinged.
2: Enter the telnet server IP port under Dos or the command line to see if it can be connected. For example, telnet 202.114.100.100 1433 usually has a port value of 1433, because 1433 is the default listening port for TCP/IP in sql Server 2000. If there is a problem, usually this step goes wrong. The usual prompt is "...... Unable to open connection, connection failed".
If this step is problematic, you should check the following options.
1) Check if the remote server has started the SQL Server 2000 service. If not, it starts.
2) Check whether the Tcp/IP protocol is enabled on the server side, because remote connections (via the Internet) need to rely on this protocol. The check method is to open the Start Menu-> Programs-> Microsoft SQL Server-> Server Network Utility on the server to see if there is a TCP/IP protocol in the enabled protocol, and if not, enable it.
3) Check whether the TCP/IP port of the server is configured as port 1433. Still check the TCP/IP attribute in the Enable protocol in the Server Network Utility, make sure the default port is 1433, and the Hide Server checkbox is not checked. In fact, it is possible if the default port is modified, but when the client does telnet testing, the server port number must be written the same as the port number configured by the server. If the Hide Server checkbox is checked, it means that the client cannot see the server by enumerating the server, which protects but does not affect the connection, but the default port of the Tcp/ip protocol will be implicitly changed to 2433, which must be changed accordingly when the client connects.
4) If the server-side operating system has been patched with SP2, it is necessary to configure the Windows firewall to a certain extent, and to open port 1433 to it, you can usually turn off the Windows firewall directly during the test (it is best to turn off other firewalls as well).
5) Check if the server is listening on port 1433. If the server is not listening on port 1433 of the TCP connection, it cannot connect. The check method is to enter netstat -a -n or netstat -an under the DOS or command line of the server, and check the result list to see if there is an item similar to tcp 127.0.0.1 1433 listening. If not, you usually need to patch SQL Server 2000 with at least SP3. In fact, you can start the query analyzer on the server side and enter select @@version to execute it, and you can see the version number, and all versions below 8.0.2039 need to be patched. If all of the above is fine, then you can do the telnet server IP 1433 test, and you will see the cursor flashing in the upper left corner after the screen flashes. Congratulations, you can start connecting in Enterprise Manager or Query Analyzer right away.
3: Check the client settings program-> Microsoft SQL Server -> client network usage tools. As in Server Networking Utilities, make sure that the client TCP/IP protocol is enabled and that the default port is 1433 (or other ports, consistent with the server).
4: Test the connection in Enterprise Manager or Query the analyzer Enterprise Manager - > Right-click SQlserver Group - > Create a new sqlserver registration - > Next-> Write to remote IP-> Next-> Select Sqlserver login-> Next-> Write login name and password (sa, password)-> Next-> Next-> Complete Query Analyzer-> File-> Connection-> Write to remote IP-> Write login and password (sa, password)-> OK is usually recommended in the 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. How to modify the default connection timeout: Enterprise Manager - > Tools - > Options - > In the "SQL Server Enterprise Manager Properties" window that pops up, click the "Advanced" tab - > Connection Settings - > Enter a larger number in the box after Login Timeout (seconds) Query Analyzer - > Tools - > Options - > Connections - > Enter a larger number in the box after Login Timeout (seconds) Normally, you can connect, if it prompts an error, then go to the next step.
5: The error is usually caused by SQL Server using a "Windows only" authentication method, so the user cannot connect using a SQL Server 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 then select the Security tab.
3) Under Authentication, select SQL Server and Windows.
4) Restart the SQL Server service. (Net Stop MSSQLSusStop Service under DOS or Command Line, Net Start MSSQLSuttServerStop Service, is also a quick way).
|
Posted on 11/19/2016 9:15:45 AM
|
|
|
