What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?

Question

've installed SQL Express on my PC hoping to do some practice creating tables and then modifying them. I coded a webpage in Visual Studio to, basically, SELECT * from a table in the SQLEXPRESS, but I can never get the connection string to work. Please help

My connection string

"Data Source=localhost\SQLEXPRESS;Initial Catalog=test;User Id=xaa9-PC\xaa9;Password=abcd;"

Error Message:

Query is select * from tblCustomers where username='johndoe' error is Login failed for user 'x309-PC\x309'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Query is select * from tblCustomers where username='johndoe' error is Login failed for user 'x309-PC\x309'.

Check out [ConnectionStrings.com](http://www.connectionstrings.com) - it shows all possible combinations and variations of SQL Server connection strings known to mankind... — marc_s, Mar 12 '11 at 16:30

score 62 · Accepted Answer · answered Mar 12 '11 at 16:28

62

Try using Windows authentication:

Data Source=localhost\SQLEXPRESS;Initial Catalog=test;Integrated Security=SSPI;

answered Mar 12 '11 at 16:28

Andomar

232,371
49
380
404

score 8 · Answer 2 · answered Mar 12 '11 at 16:26

8

Try like this:

string connectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=test;User Id=x309;Password=abcd;";

Also make sure you have enabled SQL authentication.

answered Mar 12 '11 at 16:26

Darin Dimitrov

1,023,142
271
3,287
2,928

1

That's a Windows DOMAIN\USERNAME so SQL authentication is not the way to go, you need integrated authentication. – Ben Mar 12 '11 at 16:29
@Ben, with Windows Authentication you don't specify a username and password in the connection string. – Darin Dimitrov Mar 12 '11 at 16:30
Escaping is not required in connection strings. I assume the string isn't in C# since the compiler would give an `Unrecognized escape sequence` error for \S – Andomar Mar 12 '11 at 16:33
@Andomar, I think there is a problem with `\x` in the connection string because it is considered as HEX. – Darin Dimitrov Mar 12 '11 at 16:34
@Darin Dimitrov: Looks like backslash is not an allowed character in SQL Server logins. So he's probably trying to log in using Windows authentication, and his PC is called `xaa9-PC` – Andomar Mar 12 '11 at 16:38
@Andomar, then why in the error message it is mentioned `x309-PC`? – Darin Dimitrov Mar 12 '11 at 16:39
@Darin, That's right. What I meant to say was because this is a windows username, that means you cannot use SQL authentication, you have to use Integrated authentication. – Ben Mar 12 '11 at 16:39
@Darin Dimitrov: If you try to login using username `a\b` you'll get `Login failed for user 'a\b'.`. And there can be no such user. SQL Server does not allow you to create a login called `a\b`. – Andomar Mar 12 '11 at 16:42

score 0 · Answer 3 · edited Jan 27 '17 at 14:29

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=SHANU-PC\SQLEXPRESS;Initial Catalog=Anusha;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {

        con.Open();
        SqlCommand cmd=new SqlCommand("select * from tbl_state",con);

        SqlDataAdapter da=new SqlDataAdapter(cmd);

        DataTable dt=new DataTable();
        da.Fill(dt);
            DropDownList1.DataSource = dt;
            DropDownList1.DataTextField = "sname";
            DropDownList1.DataValueField = "sid";
            DropDownList1.DataBind();

        con.Close();
        }

score 0 · Answer 4 · edited Mar 12 '11 at 16:37

If you are placing your data connection string in a web.config file you specify your connection like below:

<connectionStrings>
<add name="NorthwindConnString" 
     connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" 
     providerName="System.Data.SqlClient"/>
</connectionStrings>

but if you are hard coding within a c# based website you have to escape the '\' back slashes:

"Data Source=.\\\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"

Even Scott Hanselman can forget this...

What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?

4 Answers4

Linked

Related