如何设置SQL Server连接字符串?

I'm developing a simple C# application, I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password etc.), but when I connect it to another PC, the SQL Server connection string is different. Is there a common account in SQL Server that come with default account that can connect? I have heard about sa account in SQL Server, what is sa?

我正在开发一个简单的c#应用程序，我想知道这一点:当我将我的应用程序连接到我的PC上的SQL Server时，我知道连接字符串(服务器名、密码等)，但是当我将它连接到另一台PC时，SQL Server连接字符串是不同的。SQL Server中是否有一个可以连接的默认帐户?我听说过SQL Server中的sa账号，sa是什么?

7 个解决方案

#1

// .NET DataProvider -- Standard Connection with username and password

.NET数据提供程序——用户名和密码的标准连接

using System.Data.SqlClient;

SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"Data Source=ServerName;" +
"Initial Catalog=DataBaseName;" +
"User id=UserName;" +
"Password=Secret;";
conn.Open();

// .NET DataProvider -- Trusted Connection

// .NET数据提供程序—可信连接

SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"Data Source=ServerName;" +
"Initial Catalog=DataBaseName;" +
"Integrated Security=SSPI;";
conn.Open();

#2

.NET Data Provider -- Default Relative Path -- Standard Connection

。net数据提供程序——默认相对路径——标准连接

 using System.Data.SqlClient;
 var conn = new SqlConnection();
 conn.ConnectionString = 
 "Data Source=.\SQLExpress;" + 
 "User Instance=true;" + 
 "User Id=UserName;" + 
 "Password=Secret;" + 
 "AttachDbFilename=|DataDirectory|DataBaseName.mdf;"conn.Open();

.NET Data Provider -- Default Relative Path -- Trusted Connection

.NET数据提供程序——默认的相对路径——可信连接。

 using System.Data.SqlClient;
 var conn = new SqlConnection();
 conn.ConnectionString = 
 "Data Source=.\SQLExpress;" + 
 "User Instance=true;" + 
 "Integrated Security=true;" + 
 "AttachDbFilename=|DataDirectory|DataBaseName.mdf;" conn.Open();

.NET Data Provider -- Custom Relative Path -- Standard Connection

。net数据提供程序——自定义相对路径——标准连接

using System.Data.SqlClient;
AppDomain.CurrentDomain.SetData(
"DataDirectory", "C:\MyPath\");
 var conn = new SqlConnection();
 conn.ConnectionString = 
 "Data Source=.\SQLExpress;" + 
 "User Instance=true;" + 
 "User Id=UserName;" + 
 "Password=Secret;" + 
"AttachDbFilename=|DataDirectory|DataBaseName.mdf;" conn.Open();  

.NET Data Provider -- Custom Relative Path -- Trusted Connection

。net数据提供程序——自定义相对路径——可信连接

 using System.Data.SqlClient;
 AppDomain.CurrentDomain.SetData(
 "DataDirectory", "C:\MyPath\");
 var conn = new SqlConnection();
 conn.ConnectionString = 
 "Data Source=.\SQLExpress;" + 
 "User Instance=true;" + 
 "Integrated Security=true;" + 
 "AttachDbFilename=|DataDirectory|DataBaseName.mdf;" conn.Open();

#3

They are a number of things to worry about when connecting to SQL Server on another machine.

当连接到另一台机器上的SQL Server时，需要考虑很多问题。

• Host/IP Address of the machine
• 机器的主机/IP地址
• Initial Catalog (database name)
• 初始目录(数据库名称)
• Valid username/password
• 有效的用户名/密码

Very often SQL server may be running as a default intance which means you can simply specify the hostname/ip address but you may encounter a scenario where it is running as a named instance (Sql Express for instance). In this scenario you'll have to specify hostname\instance name .

通常，SQL server可能作为默认的intance运行，这意味着您可以简单地指定主机名/ip地址，但是您可能会遇到这样的场景:它作为一个命名实例(例如SQL Express)运行。在这个场景中，您必须指定主机名\实例名。

#4

You need to understand that a database server or DBA would not want just anyone to be able to connect or modify the contents of the server. This is the whole purpose of security accounts. If a single username/pwd would work on just any machine, it would provide no protection. That "sa" thing you have heard of, does not work with SQL Server 2005, 2008 or 2012. Not sure about previous versions though. I believe somewhere in the early days of SQL Server, the default username and pwd used to be sa/sa, but that is no longer the case.

您需要理解，数据库服务器或DBA不希望任何人能够连接或修改服务器的内容。这就是安全账户的全部目的。如果一个用户名/pwd能在任何机器上工作，它就不会提供任何保护。您听说过的“sa”并不适用于SQL Server 2005、2008或2012。但不确定以前的版本。我相信在SQL Server的早期，默认的用户名和pwd曾经是sa/sa，但现在已经不是这样了。

FYI, database security and roles are much more complicated now-a-days. You may want to look into the details of Windows-based authentication. If your SQL Server is configured for it, you don't need any username/pwd in the connection string to connect to it. All you need to change is the server machine name and the same connection string will work with both your machines, given both have same db name of course.

顺便说一句，数据库安全和角色现在变得复杂得多。您可能需要查看基于windows的身份验证的详细信息。如果您的SQL服务器是为此配置的，那么您不需要连接字符串中的任何用户名/pwd来连接它。您需要更改的是服务器机器名，相同的连接字符串将与您的机器一起工作，因为两者都有相同的db名称。

#5

You can use either Windows authentification, if your server is in Domain, or Sql authentification. Sa - is a System Administratior, the root account for SQL server authentification. But it is a bad practice to use if for conneting of your clients. You should create your own accounts, and use them to connect to your SQL. In each connection you set account login, its password and the default database, you want to connect.

如果您的服务器在域中，您可以使用Windows认证，也可以使用Sql认证。Sa -是一个系统管理员，是SQL服务器认证的根帐户。但是用if来欺骗你的客户是不好的做法。您应该创建自己的帐户，并使用它们连接到您的SQL。在每个连接中，您都要设置帐户登录、其密码和默认数据库。

#6

Actually you can use the SqlConnectionStringBuilder class to build your connection string. To build the connection string, you need to instantiate an object from that SqlConnectionStringBuilder and set their properties with the parameters you use to connect to the DataBase. Then you can get the connection string from the ConnectionString property from the SqlConnectionStringBuilder object, as is shown in this example:

实际上，您可以使用SqlConnectionStringBuilder类来构建连接字符串。要构建连接字符串，需要从SqlConnectionStringBuilder实例化一个对象，并使用连接到数据库的参数设置其属性。然后，您可以从SqlConnectionStringBuilder对象的ConnectionString属性获得连接字符串，如下例所示:

For example:

例如:

    SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder ()
    {
        DataSource = "ServerName",
        InitialCatalog = "DatabaseName",
        UserID = "UserName",
        Password = "UserPassword"
    }.ConnectionString

SqlConnection conn = new SqlConnection(sConnB.ConnectionString);

You can either use the new operator to make that directly.

你可以直接使用新的运算符。

For example:

例如:

SqlConnection conn = new SqlConnection(
    new SqlConnectionStringBuilder ()
    {
        DataSource = "ServerName",
        InitialCatalog = "DatabaseName",
        UserID = "UserName",
        Password = "UserPassword"
    }.ConnectionString
);

You can add more parameters to build your connection string. Remember that the parameters are defined by the values setted in the SqlConnectionStringBuilder object properties.

您可以添加更多的参数来构建连接字符串。记住，参数是由SqlConnectionStringBuilder对象属性中设置的值定义的。

Also you can get the database connection string from the conection of Microsoft Visual Studio with the attached DB. When you select the DB, in the properties panel is shown the connection string.

还可以从Microsoft Visual Studio的conection中获取数据库连接字符串，并附带DB。当您选择DB时，在properties面板中显示连接字符串。

The complete list of properties of the SqlConnectionStringBuilder class is listed in this page from the Microsoft MSDN site.

SqlConnectionStringBuilder类的完整属性列表在这个来自Microsoft MSDN站点的页面中列出。

About the default user of SQL Server, sa means "system-administrator" and its password varies according the SQL Server version. In this page you can see how the password varies.

对于SQL Server的默认用户，sa表示“系统管理员”，其密码根据SQL Server版本不同而不同。在这个页面中，您可以看到密码是如何变化的。

SQL Server 2008/R2 Express User: sa Password: [blank password - leave field empty to connect]

SQL Server 2008/R2 Express用户:sa密码:[空白密码-留空字段连接]

SQL Server 201x Express User: sa Password: Password123

SQL Server 201x Express用户:sa密码:Password123

SQL Server 20xx Web or Standard User: sa Password: will be the same as your administrator or root user password at the time the VDS was provisioned.

SQL Server 20xx Web或标准用户:sa密码:将与提供VDS时的管理员或根用户密码相同。

You can log in with sa user in this login window at the start of SQL Server Database Manager. Like in this image:

在SQL Server数据库管理器的启动处，您可以在这个登录窗口中与sa用户登录。像这张图片:

#7

sa is a system administrator account which comes with sql server by default. As you know might already know, you can use two ways to log in to SQL Server.

sa是一个系统管理员帐户，默认情况下附带sql server。您可能已经知道，您可以使用两种方法登录到SQL Server。

Therefore there are connection strings which suitable for each scenario(such as windows authentication, localdb etc.). Use https://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx#sqlserver to build your connection string. These are XML tags. You just need value of connectionString

因此，有适合每个场景的连接字符串(例如windows身份验证、localdb等)。使用https://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx#sqlserver来构建连接字符串。这些都是XML标记。你只需要connectionString的值