How can I set an SQL Server connection string?

I'm developing a simple C# application, and 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 comes with a default account that can connect?

I have heard about the sa account in SQL Server. What is sa?

Solution 1:

.NET DataProvider -- Standard Connection with username and password

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

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

Refer to the documentation.

Solution 2:

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:

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.

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

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

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

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.

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

Solution 3:

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

 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

 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

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

 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();

Solution 4:

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";