sqlConnetion() failed with parameters which make .udl file succeed to connect

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I set up a SQL Server Express on Windows Server 2008 and created some database & tables on it. I used a .udl file to test connectivity to it remotely and it's successful:
In .udl file, the SQL server shown is <my server="">/SQLEXPRESS, user name & password to login to server are: sa & <my password="">.

Then, I wrote a C# program to do connection. The first connection fails, but its parameters are totally as the ones used in .udl connectivity:

C#

SqlConnection conn = new SqlConnection("Data Source=STAN-MTP-SQL\\SQLEXPRESS; Database=MTP_TESTDB1;Integrated Security=true;User Id=sa;Password=<My password>");
try { conn.Open(); }

This kind of connection way appears at lots of sample programs in web. But it failed. The error message is: "ClientConnectionId:af804f7a-8485-4cc3-babb-le3b32546c92 Exception caught."

So, the two ways (.udl & C# program) used the same parameters to connect remotely. The test situation are the same. How come .udl succeeded but C# program failed?

Posted 4-Jan-15 20:49pm

Stan Huang

Add a Solution

Comments

Arjsrya 5-Jan-15 2:52am

Try this.

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Database=MTP_TESTDB1;User Id=sa;Password=<my password="">");

CHill60 5-Jan-15 2:57am

That error message looks like one you've written into the catch clause of that try - what is the actual exception message? Hint - instead of "Exception caught" try using ex.Message

Stan Huang 5-Jan-15 3:01am

I used ex.Message and the error message shown is "User 'sa' login failed" Except caught.

CHill60 5-Jan-15 3:07am

You have both Integrated Security=true AND a userid and password in your connection - use one or the other not both. Also I've never seen User and Password capitalised - might make a difference

Stan Huang 5-Jan-15 2:58am

Arjsrya, same result.

Arjsrya 5-Jan-15 3:00am

Could you share the code of sql connection?Are you able to access the sql server using windows aunthentication?

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Database=MTP_TESTDB1;Integrated Security=true");

Stan Huang 5-Jan-15 3:05am

I can't connect using .udl with Windows authentication, but I can connect using .udl with SQL Login (sa / <my password="">) successfully.
My code snippet:
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Database=MTP_TESTDB1;User Id=sa;Password=stan");
try { conn.Open(); }
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e.Message);
Console.WriteLine("connection failed!");
string xx = Console.ReadLine();
}
Console.WriteLine("connection succeedded!");
string yy = Console.ReadLine();

Arjsrya 5-Jan-15 3:14am

Don't find anything wrong in your code.What exception are you getting?

Try these 2 connection strings.
1. "Data Source=.\SqlExpress;Initial Catalog=MTP_TESTDB1;User ID=UserName;Password=Password"

2. "Data Source=(local);Initial Catalog=MTP_TESTDB1;User ID=UserName;Password=Password"

Have you tried to connect sql database using SQl Server Management Studio?

Stan Huang 5-Jan-15 3:24am

User 'sa' login failed.
I used a .udl file to confirm the connection parameters correct.

Arjsrya 5-Jan-15 4:33am

Could you please try to login with windows credentials? After login,Just try to change the password for SA user and Give full permission to that user.

Stan Huang 5-Jan-15 6:17am

Do you mean login from my client computer to SQL server with Windows credentials and then change SQL server from client computer?
In SQL server, at "Stauts//Settings//permission to connect to database engine", I set "Grant". Doesn't it give it full permission?

Kornfeld Eliyahu Peter 5-Jan-15 4:38am

Open your UDL file and examine the connection string stored in...
Do not use integrated and username/password based authentication at the same time...

Stan Huang 5-Jan-15 4:49am

I opened my UDL file but I can't find so-called "connection string".

Kornfeld Eliyahu Peter 5-Jan-15 4:53am

An UDL file should look like this:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=a-iresmi2000;Initial Catalog=Northwind;Integrated Security=SSPI

Can you share yours with us? Maybe someone will spot the problem...

Stan Huang 5-Jan-15 4:58am

1. How can I find such string? I type under MS-DOS or opened it using text editor and found it's empty.
2. How can I share it to others in CodeProject?

Kornfeld Eliyahu Peter 5-Jan-15 5:01am

1. Are you telling us that the udl file is empty? Please check this: http://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx
2. Copy the content (as text) into your question...

Stan Huang 5-Jan-15 5:26am

I followed steps in http://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx, but I can't find string like:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=a-iresmi2000;Initial Catalog=Northwind;Integrated Security=SSPI

How to open it? with text editor?

Kornfeld Eliyahu Peter 5-Jan-15 5:27am

Do you have the UDL file? Right click on it and use 'Open with'...

Stan Huang 5-Jan-15 5:31am

I do have the UDL file and test connectivity well. I opened it with "OLE DB Core Services" well. But I tried every tab and didn't find any such string. Anything wrong?

Stan Huang 8-Jan-15 1:10am

It works: SqlConnection conn = new SqlConnection(@"Server=192.168.8.98\SQLEXPRESS; Database=MTP_TESTDB1;User Id=sa;Password=stan");

I mistakenly added "\\" before 192.168.8.98.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Stan Huang · Accepted Answer · 2015-01-07T19:14:00

Solution 2

It works: SqlConnection conn = new SqlConnection(@"Server=192.168.8.98\SQLEXPRESS; Database=MTP_TESTDB1;User Id=sa;Password=stan");

I mistakenly added "\\" before 192.168.8.98

Posted 7-Jan-15 19:14pm