C# 如何设置 SQL Server 连接字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15631602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set SQL Server connection string?
提问by Roshan
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 saaccount in SQL Server, what is sa?
我正在开发一个简单的 C# 应用程序，我想知道这一点：当我将我的应用程序连接到我的 PC 上的 SQL Server 时，我知道连接字符串（服务器名称、密码等），但是当我将它连接到另一个PC，SQL Server 连接字符串不同。SQL Server 中是否有可以连接的默认帐户的通用帐户？我听说过saSQL Server 中的帐户，它是什么sa？
回答by scartag
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
- Initial Catalog (database name)
- Valid username/password
- 机器的主机/IP地址
- 初始目录（数据库名称）
- 有效的用户名/密码
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 服务器可能作为默认实例运行，这意味着您可以简单地指定主机名/IP 地址，但您可能会遇到它作为命名实例（例如 Sql Express）运行的情况。在这种情况下，您必须指定 hostname\instance name 。
回答by dotNET
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 不希望任何人都能够连接或修改服务器的内容。这就是安全帐户的全部目的。如果单个用户名/密码只能在任何机器上工作，则它不会提供任何保护。您听说过的“sa”东西不适用于 SQL Server 2005、2008 或 2012。不过不确定以前的版本。我相信在 SQL Server 早期的某个地方，默认用户名和密码曾经是 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 Server 已为其配置，则连接字符串中不需要任何用户名/密码即可连接到它。您需要更改的只是服务器机器名称，并且相同的连接字符串将适用于您的两台机器，当然，它们都具有相同的数据库名称。
回答by Alex
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 passwordand the default database, you want to connect.
您可以使用 Windows 身份验证（如果您的服务器在域中）或 Sql 身份验证。Sa - 是系统管理员，SQL 服务器身份验证的 root 帐户。但是使用 if 来连接您的客户是一种不好的做法。您应该创建自己的帐户，并使用它们连接到您的 SQL。在每个连接中设置您要连接的帐户登录名、密码和默认数据库。
回答by Itachi
.NET DataProvider -- Standard Connection with username and password
.NET DataProvider -- 使用用户名和密码的标准连接
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();
Refer to doc.
请参阅文档。
回答by Hemlata Gehlot
.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();
回答by Menuka Ishan
sais 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#sqlserverto 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 的值
回答by CryogenicNeo
Actually you can use the SqlConnectionStringBuilderclass to build your connection string. To build the connection string, you need to instantiate an object from that SqlConnectionStringBuilderand set their properties with the parameters you use to connect to the DataBase. Then you can get the connection stringfrom the ConnectionStringproperty from the SqlConnectionStringBuilderobject, as is shown in this example:
实际上，您可以使用SqlConnectionStringBuilder该类来构建连接字符串。要构建连接字符串，您需要从中实例化一个对象，SqlConnectionStringBuilder并使用用于连接到数据库的参数设置它们的属性。然后您可以从对象的属性中获取连接字符串，如下例所示：ConnectionStringSqlConnectionStringBuilder
For example:
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder () { DataSource = "ServerName", InitialCatalog = "DatabaseName", UserID = "UserName", Password = "UserPassword" }.ConnectionString SqlConnection conn = new SqlConnection(sConnB.ConnectionString);
例如：
SqlConnectionStringBuilder sConnB = new SqlConnectionStringBuilder () { DataSource = "ServerName", InitialCatalog = "DatabaseName", UserID = "UserName", Password = "UserPassword" }.ConnectionString SqlConnection conn = new SqlConnection(sConnB.ConnectionString);
You can either use the newoperator to make that directly.
您可以使用new运算符直接进行操作。
For example:
SqlConnection conn = new SqlConnection( new SqlConnectionStringBuilder () { DataSource = "ServerName", InitialCatalog = "DatabaseName", UserID = "UserName", Password = "UserPassword" }.ConnectionString );
例如：
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 SqlConnectionStringBuilderobject 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 与附加 DB 的连接中获取数据库连接字符串。当您选择 DB 时，属性面板中会显示连接字符串。
The complete list of properties of the SqlConnectionStringBuilderclass is listed in this page from the Microsoft MSDN site.
Microsoft MSDN 站点的SqlConnectionStringBuilder此页面中列出了该类的完整属性列表。
About the default user of SQL Server, sameans "system-administrator" and its password varies according the SQL Server version. In this pageyou 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 201x Express User:sa Password: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 2008/R2 Express 用户：sa 密码：[空白密码 - 将字段留空以连接]
SQL Server 201x Express 用户：sa 密码：Password123
SQL Server 20xx Web 或标准用户：sa 密码：将与供应 VDS 时的管理员或 root 用户密码相同。
You can log in with sauser in this login window at the start of SQL Server Database Manager. Like in this image:
您可以在SQL Server 数据库管理器启动时在此登录窗口中使用sa用户登录。就像这张图片：
回答by Chamila Maddumage
You can use the connection string as follows and you only need to add your database name.
您可以按如下方式使用连接字符串，您只需要添加您的数据库名称。
string connetionString = "Data Source=.;Initial Catalog=DB name;Integrated Security=True;MultipleActiveResultSets=True";
回答by V L B DE MEL
"ConnectionString":{
"Database_Name": "server=.;database=Database_Name;Integrated Security=true;"
},
Try this one
试试这个
回答by Praneeth Gopinathan
We can simply connect to database:
我们可以简单地连接到数据库：
uid=username;pwd=password;database=databasename;server=servername
E.g.:
例如：
string connectionString = @"uid=spacecraftU1;pwd=Appolo11;
database=spacecraft_db;
server=DESKTOP-99K0FRS\PRANEETHDB";
SqlConnection con = new SqlConnection(connectionString);
