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?

You never want to use a default account username and password to set up access to SQL Server or any connection type for that matter. — jordanhill123, Mar 26 '13 at 07:33
Can you use Windows Authentication instead of SQL Server authentication? That would avoid having to use userids and passwords altogether. — Philip Sheard, Mar 26 '13 at 07:35

score 160 · Answer 1 · edited Dec 08 '20 at 08:04

160

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

edited Dec 08 '20 at 08:04

Peter Mortensen

30,738
21
105
131

answered Mar 26 '13 at 08:21

Itachi

5,777
2
37
69

1

How can I use this format but use a domain user? I keep get a red underline when I use `user id=Domain\Uname` i think it's something to do with invalid escape characters, how should I do that corectly? – Wairimu Murigi May 23 '16 at 15:29
1

@Wairimu Murigi You have to escape the backslash i.e user id=Domain\\Uname – John Hartley Jun 20 '16 at 13:03
1

@Itachi: Sorry for the necropost. Do we enter this in the Windows command line using SQLCMD? – MSIS Jan 16 '20 at 20:07
1

My password contains ***`;`*** character – Kiquenet Feb 27 '20 at 12:52
1

@Kiquenet You could try single or double quotes to wrap it, check [this](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings?view=netframework-4.8) out. – Itachi Feb 27 '20 at 15:23
Does the credentials go from client to sql sqlserver as plain text? If so is there any way to secure it? – variable Jan 14 '22 at 14:55

score 44 · Answer 2 · edited Dec 08 '20 at 08:15

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:

score 16 · Answer 3 · answered May 08 '17 at 07:19

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

Can you fix the code so that it actually compiles? You can [edit your answer](https://stackoverflow.com/posts/43841898/edit) (but ***without*** "Edit:", "Update:", or similar). — Peter Mortensen, Dec 08 '20 at 08:08

score 11 · Answer 4 · answered Jul 26 '18 at 09:26

11

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

answered Jul 26 '18 at 09:26

Chamila Maddumage

3,304
2
32
43

score 8 · Answer 5 · edited Dec 08 '20 at 07:49

8

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.

edited Dec 08 '20 at 07:49

Peter Mortensen

30,738
21
105
131

answered Mar 26 '13 at 07:33

scartag

17,548
3
48
52

Does the credentials go from client to sql sqlserver as plain text? If so is there any way to secure it? – variable Jan 14 '22 at 14:55

score 7 · Answer 6 · edited Dec 08 '20 at 08:19

7

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

edited Dec 08 '20 at 08:19

Peter Mortensen

30,738
21
105
131

answered Jul 15 '19 at 07:47

Praneeth T T

305
3
9

Is that an actual username/password pair? – Peter Mortensen Dec 08 '20 at 08:20

score 6 · Answer 7 · edited Dec 08 '20 at 08:01

6

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.

edited Dec 08 '20 at 08:01

Peter Mortensen

30,738
21
105
131

answered Mar 26 '13 at 07:36

Alex

8,827
3
42
58

"SQL" is not a valid shortcut for "SQL Server". – Peter Mortensen Dec 08 '20 at 08:03

score 6 · Answer 8 · edited Dec 08 '20 at 08:12

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.

score 5 · Answer 9 · edited Dec 08 '20 at 07:54

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.

sa is the sysadmin account for SQL Server. If it was installed with sql server authentication, or mixed mode authentication, you're required to set up an sa account. For future reference, [here's a guide](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode) on how to set up the sa account if it wasn't installed with sql server authentication turned on. — iCodeSometime, Aug 07 '17 at 04:26

score 2 · Answer 10 · answered Oct 04 '22 at 09:40

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",
 }
}

How can I set an SQL Server connection string?

10 Answers10

.NET DataProvider -- Standard Connection with username and password

.NET DataProvider -- Trusted Connection

Linked

Related