How can I set an SQL Server connection string?

Question

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?

Answer 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.

Answer 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:

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

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

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 instance 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 Server Express Edition for instance). In this scenario you'll have to specify the hostname/instance name.

Answer 6

We can simply connect to the database like this:

 uid=username;pwd=password;database=databasename;server=servername

For example:

string connectionString = @"uid=spacecraftU1;pwd=Appolo11;
                            database=spacecraft_db;
                            server=DESKTOP-99K0FRS\\PRANEETHDB";
SqlConnection con = new SqlConnection(connectionString);

Answer 7

You can use either Windows authentication, if your server is in the domain, or SQL Server authentication. Sa is a system administrator, the root account for SQL Server authentication. But it is a bad practice to use if for connecting to your clients.

You should create your own accounts, and use them to connect to your SQL Server instance. In each connection you set account login, its password and the default database, you want to connect to.

Answer 8

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.

Therefore there are connection strings which suitable for each scenario (such as Windows authentication, localdb, etc.). Use SQL Server Connection Strings for ASP.NET Web Applications to build your connection string. These are XML tags. You just need a value of connectionString.

Answer 9

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/password 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. I am not sure about previous versions though. I believe somewhere in the early days of SQL Server, the default username and password used to be sa/sa, but that is no longer the case.

FYI, database security and roles are much more complicated nowadays. 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/password 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 database name of course.

Answer 10

If anyone is looking for .NET Core solution, you will need to set sql server connection string in appsettings.json

{
"ConnectionStrings": {
 "SQLServerAuth": "Data Source=Desktop-11\\SQL2017;Initial Catalog=SampleDB; User ID=sa;Password=pass1234"
 }
}

Source: SQL Server Connection String Examples

For Windows Auth Connection string would be as below in Appsettings.json

{
"ConnectionStrings": {
 "WindowsAuth": "Data Source=Desktop-11\\SQL2017;Initial Catalog=SampleDB; Integrated Security=true",
 }
}