Sql-server Configuring remote connections and C # connection strings

SQL Server 2008 does not allow remote connections by default, the SA account is disabled by default, and if you want to connect to SQL Server 2008 on a remote server with SSMs locally, you need to do a two-part configuration: Use the SA account to log on to SQL Server Management Studio (SSMS) If you are not using SQL Server authentication mode when you install, use the Windows Identity Connection database, log on, and then right-select Properties

Security, select SQL Server and Windows authentication mode on the right to enable hybrid login mode

Select "Connect", check "Allow remote connection to this server", recommend "Maximum concurrent connections" to the actual situation, set, then click "OK"

Expand Security, login name, SA, right-select Properties

On the left, select General, select SQL Server authentication on the right, and set the password

Select "Status", select "Enable", click "OK"

Right-click on the database Select "Facet"

In the Facet next list box, select Server Configuration, set the Remoteaccessenabled property and remotodacenabled to True, and then click OK

Now that SSMs is set up, exit and log in with SA, success means that the SA account is enabled. Start by configuring SQL Server Configuration Manager (SSCM), check the SQL Server service on the left, and make sure that SQL Server on the right and SQL Server Browser are running

Open the left SQL Server network configuration, open the protocol for your own DB instance name, and view the TCP/IP default on the right is "disabled" and modify it to "enabled"

Double-click to open TCP/IP to view all listening and enabled items in the Protocols tab under TCP/IP properties, whether they are set to Yes

Select the IP addersses tab, IP1, IP2, Ipall set the TCP port to "1433", the TCP dynamic port is a null value, and the "Yes" is enabled

Modify TCP/IP of the client protocol to Enabled

Double-click to open TCP/IP on the right, open TCP/IP properties, set the default port to "1433", "Yes" enabled

When the configuration is complete, restart SQL Server 2008. In the final step, you will need to map the SQL Server 1433 TCP port and the 1434 UDP port on the firewall. Note: After SQL Server 2005/2008 Express is installed, remote connections are disabled by default. If remote access is required, manual configuration is required. Open the firewall settings. The SQLServr.exe (C:\Program Files\Microsoft SQL Server\mssql10. Sqlexpress\mssql\binn\sqlservr.exe) is added to the Allowed list. The article is based on the actual situation I used to conduct continuous testing and on-line search for relevant information to rectify. Because I use the CiscoASA5510 firewall, so in the configuration of the firewall once thought as long as 1433 of the TCP port mapping is possible, but the trial is unsuccessful. Finally, the data of the SQL Server network port is searched, and the UDP port of 1434 is mapped out, so it can be successfully connected in the Internet with SSMs. C # Connection stringpublic static string strconnection = "Data source=*.*.*.*;initial catalog=mcudata;user id=sa;password=sa;";

Integrated Security = True what does it mean?

Integrated security=true means integrated authentication, which means using Windows Authentication to connect to the database server. The benefit of this approach is that there is no need to write the user name and password in the connection string, to some extent, improve security.

problems that may arise:

SQL 2005 User SA logon failure, the user is not associated with a trusted SQL Server connection error 18452

Solution Solutions

User ' sa ' login failed. The user is not associated with a trusted SQL Server connection

issue One, forgot login password for the SA that logged on to Microsoft SQL Server 2005

Workaround: log in with Windows Authentication first, then ' security '-' login '-right-click ' sa '-' Properties ', Change password (sa password can not be too simple, it is best to have numbers, letters, underscore characters, and the length is preferably greater than 10 characters); Click OK.

problem two, the connection to the server has been successfully established, but a mistake occurred during the logon process. (Provider: Shared Memory provider, no process on the other end of the error:0-pipeline.) ) (Microsoft SQL Server, error: 233)

Workaround: open ' programs '-' All Programs '-' Microsoft SQL Server 2005 '-' Configuration Tools '-' SQL Server Configuration Manager ', in the popup form, locate ' SQL Server 2005 Network configuration ', put ' MSSQLServer the protocol ' Named Pipes ' and ' TCP/IP ' start, and then restart Microsoft SQL Server 2005.

problem three, unable to open user default database. Login failed. User ' sa ' login failed. (Microsoft SQL Server, error: 4064)

Workaround: first log in with Windows Authentication, then in ' security '-' login '-right-click ' sa '-' properties ', set the default database to master, click OK.

issue four,SQL Server 2005 Error 18452

Unable to connect to server

Server: Msg 18452, Level 16, State 1

[Microsoft] [ODBC SQL Server Driver] [SQL Server] user ' sa ' failed to log on. Cause: not associated with a trusted SQL Server connection

This error occurs because SQL Server uses Windows-only authentication so that users cannot connect using SQL Server's login account (for example, SA), as follows

Set Allow SQL Server login (basically this is useful)

Operation Steps:

1. In Enterprise Manager, expand SQL Server Group, right-click the name of your SQL Server server

2. Select "Properties"

3. Then select the Security tab

4. Under Authentication, select SQL Server and Windows

5. OK, and restart the SQL Server service

issue five, user ' sa ' login failed. The user is not associated with a trusted SQL Server connection.

Workaround: Check the authentication mode of your database, Windows and mixed mode, please select mixed mode for SA login.

Check the computer 1433 connection port, 1434 data port is open

For SQL 2005 Access Manager "Security" = = "user" = = "Double-click User (Pop-up properties dialog) = =" status "change state to enable, exit Manager re-login (user authentication mode)

That is: Right-click the Database Properties dialog box, select the Security tab, Server Authentication mode select SQL Server and Windows Authentication mode. Then reconfigure the sa login information.

SQL Server 2005 failed to log on using SA-indicates that the user is not associated with a trusted SQL Server connection

Error message:

SA login failed, prompting the user to connect to a trusted SQL Server connection is not associated

Workaround:

Open SQL Server Management Studio Express,

Right click on the server, select Properties, click Security in the Pop-up window to switch to the security panel,

Authenticating server authentication servers from Windows Authentication mode (Windows user authentication mode)

Modify the SQL Server and Windows Authentication mode (SQL Server and Windows Authentication modes), OK.

Open Security (security)--logins (login), right-click the SA, select Properties, tap Status to switch to the status panel and set login (login) to Enabled.

Remember: Be sure to restart the SQL2005 service before it takes effect.

Sql-server Configuring remote connections and C # connection strings