How to set SQL Server connection string?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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 conection of Microsoft Visual Studio with the attached DB. When you select the DB, 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. In 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 sa user in this login window at the start of SQL Server Database Manager. Like in this image:

Answer 4

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

Answer 5

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

• Host/IP Address of the machine
• 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 .

Answer 6

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.

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.