Sql-Server configure the remote connection and C# connection string

SQLServer2008Set upOpenINTERNETThe remote connection (“ ” have ready themselves will not write)

SQL Server 2008 default is not to allow remote connections, the SA account is disabled by default, if you want to in the local SSMS connection with a remote server of SQL Server 2008, need to do two part configuration:
Use the SA account login SQL Server Management Studio (abbreviated SSMS)
If the installation, not to use SQL Server authentication mode, use the windows identity to connect to the database, login, right-click and choose “ property”

“The security of &rdquo, select the right; “ SQL Server and Windows &rdquo authentication mode to enable mixed login mode;

Select the &ldquo connection; ”, check the “ allow remote connections to the server ”, &ldquo is recommended; the maximum number of concurrent connections to ” according to the actual situation, set, and then determine the “”

“ &rdquo security; -> “ -> login ” “ sa”, the right choice “ property”

The left choose “ conventional ” &ldquo SQL Server, the right choice; authentication &rdquo, and set the password;

Select the “ ”, select “ &rdquo enabled, click “ determination;”

Right click the database “.”

In the “ ” “ the list box, server configuration, ” “ RemoteAccessEnabled” attribute and the ” RemotoDacEnabled” to “ True&rdquo, “ determination;”

So far SSMS has been set, the first exit, and then the SA login, success means that the SA account has been enabled.
The Sql Server Configuration Manager configuration (SSCM), selected on the left side of the “ SQL Server &rdquo, ensure that the right service; “ SQL Server” and “ SQL Server Browser” running

Turn left on the “ SqlServer &rdquo, open the network configuration; “ your own database instance protocol ”, check on the right side of the TCP/IP default is “ &rdquo has been disabled, the modification; “ enabled”

Double click to open “ TCP/IP” see “ TCP/IP property of the ” “ ” tab in the “ all the interception of ” and “ &rdquo enabled; item, are set to “ is”

Select the “ IP Addersses” IP1, IP2, IPAll tab, set the TCP port for “ 1433”, TCP dynamic port to a null value, is enabled for “”



Fig. the "client" and "TCP/IP" is also modified for &ldquo enabled;”

Double click to open the right “ TCP/IP”, open the &ldquo TCP/IP attribute; ”, the default port is set to “ 1433”, &ldquo is enabled;”

The configuration is complete, restart SQL Server 2008.
The last step, need the SQL firewall Server 1433 TCP port and UDP port mapping out 1434.
Note: the SQL Server 2005/2008 Express is installed, the default is to disable remote connection. If you need to manually configure the remote access,.
Open the firewall settings. The SQLServr.exe (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe) added to allow list.
The article is according to the actual situation of continuous testing and use of the Internet to find information in the rectification.
Because I use is CiscoASA5510 firewall, so when configuring firewall once thought that as long as the TCP port mapping 1433 can be, but repeated experiments are not successful. Finally find the relevant material Sql Server network port, the 1434 UDP port also mapped out, you can use SSMS connection in INTERNET.
C#Connection string
public static string strConnection = "data source=*.*.*.*;initial catalog=mcudata;user id=sa;password=sa;";

Integrated Security = True is what mean?

Integrated security=true means integration verification, is validated using Windows way to connect to the database server. This approach has the advantage of not need to write the user name and password in the connection string, to some extent improved safety.

Possible problems:

SQL 2005 login failed for user SA, the user and the trusted SQL Server connection without the associated error 18452

The solution

Login failed for user'sa'. The user and trusted SQL Server connection without Association

Problem a, Forget login to the Microsoft SQL Server 2005 SA login password

Solution: To use windows authentication login in, and then in the ‘ &rsquo security; -‘ log ’ - right click on the ‘ sa’ -‘ &rsquo properties; (SA, modify the password password can not be too simple, it is best to have a number, letter, underscore characters, and the length should be greater than 10 character); click OK.

Problem two, Has been to establish a connection with the server, but wrong in the login process. (provider: shared memory provider, the other end of error:0- pipes without any process.) (Microsoft SQL Server, error: 233)

Solution: Open the ‘ ’ ‘ all program ’ ‘ Microsoft SQL Server 2005 ’ ‘ configuration tool ‘ ’ the SQL Server configuration manager’, In the pop-up window, Find the ‘ SQL Server 2005 network configuration’, The &lsquo MSSQLSERVER protocol; ’ the “ Named Pipes” and “ TCP/IP” start, And then restart Microsoft SQL Server 2005 can be.

Problem three, Unable to open the user default database. Login failed. The user ‘ sa’ logon failure. (Microsoft SQL Server, error: 4064)

Solution: To use windows authentication login in, and then in the ‘ &rsquo security; -‘ log ’ - right click on the ‘ sa’ -‘ &rsquo properties;, the default database is set to master, click OK.

Problem four, SQL Server 2005 error 18452

Unable to connect to server

Server: news 18452, level 16, state 1

[Microsoft][ODBC SQL Server Driver][SQL Server]The user ‘ sa’ logon failure. Reason: not associated with a trusted SQL Server connection

The reason for the error is due to the SQL Server using the "authentication mode only Windows", so the user cannot use SQL Server login account (such as SA) are connected, the solution is as follows

Setting allows SQL Server login (basically the useful)

Operation steps:

1. In enterprise manager, launched "SQL Server", the name of a right mouse click on the SQL Server server

2. Select "properties"

3. Then select "security" tab

4. In the "Id", select "SQL Server and Windows"

5. Sure, and restart the SQL Server service

Problem five, Login failed for user'sa'. The user and trusted SQL Server connection without Association.

Solution: Check your database authentication mode, windows and the mixed mode, need SA landing please select mixed mode.

Check whether the computer 1433 port, 1434 data ports open

According to the SQL 2005 into the manager “ ” &ldquo = = "&rdquo"; users; user double-click (= = = = "pop-up dialog box) “ ” the state changed to enable, exit manager re login (user mode)

Namely: right click the database properties dialog box, select the &ldquo &rdquo tab; security; authentication mode, the server “ SQL Server and Windows authentication mode. Login information and then re configuration of SA.

SQL SERVER 2005 using the SA login failed - prompt the user with the trusted SQL Server connection without Association

Error message:

The SA logon failed, the user is prompted and trusted SQL Server connection without Association

Solution:

Open the SQL Server Management Studio Express,

Right click on the server, select the Properties (attribute), click on Security (Security) to switch to the security panel,

Mode server authentication server certification from the windows authentication (Windows user authentication mode)

Modify the Sql Server and Windows Authentication mode (Sql server and windows authentication mode), ok.

Open the security (safety) - logins (login), the right button to select the SA, select properties (attribute), click the Status (state) to switch to the state panel, Login (log) is set to Enabled (enable).

Remember: be sure to put the SQL2005 service restart to take effect.

Posted by Marvin at November 26, 2013 - 8:57 AM