SQL Exception

[jimbledon]

Hi All,

Over the past couple of days i have been having a problem when trying to connect my Mobile Application to my database.
The error i receive is SQL Exception. Which is strange as i have transferred the code to a Windows Project and it works fine.

System is
SQL-Server 2000
WIndows XP Pro (No service packs installed!)
IIS Running
.Net Famework 1.1
Visual Studio 2003
Pocket PC Emulator 2002

The code that is failing to execute is

conn = new SqlConnection(conString);
				
				conn.Open();

				//Query and pass it to the DataAdapter
				string strCustomers = "SELECT CustId, (CustSName + ' ' + CustFName) as [CustName] FROM TblCustTable";
				daCustomers = new SqlDataAdapter(strCustomers, conn);

				dsCustomers= new DataSet();
				daCustomers.Fill(dsCustomers, "CustList");

				comboBox1.DataSource = dsCustomers.Tables["CustList"];
				comboBox1.DisplayMember = "CustName";

The code throws and sql exception at the Open() command. I have tried this both in VB and c# but to no avail.

I'm fairly inexperienced with setting up and configuring windows; i would greatly appreciate it someone could advise a method of solving my dilemma.


Many Thanks

James

 

[Rhys666]

And the exception is ???

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[chiph]

Don't forget to include the information that is in the innerException property. SQL Exceptions are often just wrappers for the real problem.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[obislavu]

Use SqlError object.
An instance of SqlError is created and managed by the SqlErrorCollection, which in turn is created by the SqlException class.
To trap the error:

try
{
   ...
   conn.Open();
   ...
}
catch (SqlException ex)
{
   for (int i=0; i < ex.Errors.Count; i++)
    {
       System.Windows.Forms.MessageBox.Show("Error index # " + i + " " + "Full Error Message: " + ex.Errors[i].ToString() );
    }
	
}

Messages with a severity level of 10 or less are informational and indicate problems caused by mistakes in information that a user has entered.
Severity levels from 11 through 16 are generated by the user, and can be corrected by the user.
Severity levels from 17 through 25 indicate software or hardware errors.
When a level 17, 18, or 19 error occurs, you can continue working, although you might not be able to execute a particular statement.
obislavu

 

[jimbledon]

Hi,

I have output the following information by using the following code. I wasn't sure how to get much more info out of it.

catch (SqlException ex)
{
	Console.Write("SqlException: "+ ex.Message);
	statusBar1.Text = ex.Message;

}

SQL Exception
Message: SQL Server does not exist or access denied.
Procedurte: ConnectionOpen(Connect())
Source: .Net SqlClient Data Provider

Thanks

James

 

[jimbledon]

Hi obislavu,

Thank you for you code, the outcome is below

Error index # 0 Full Error Message:
System.data.sqlclient.sqlerror: Sql server does not exist or access denied.

The error is 0 so i assume that the this is down to a SQL configuration.
Recently i granted the IIS user the connection string uses to own the database so anything this user wants to do is possible? The connection string is:

	private string conString = "Initial Catalog=Project;Data Source=192.168.1.51;Integrated Security=SSPI;User ID=IUSR_JAMES;Password=password";

Do you have any ideas as what else to try?

Many thanks

James

 

[oppcos]

Can you connect to the SQL database using that username and password in SQL Enterprise Manager manually (or if you don't have that, just try to connect with a new ODBC data source in Window's control panel).

 

[jimbledon]

Hi oppcos,

I had a look at the users in the Enterprise Manager and there was no IUSR_JAMES list; I created a new one and granted owner permission to the database.
I dont really under what user connects to the datadase that is defined in the connection string, my understanding was that it would be the IIS user.

Since creating this user the error has changed somewhat to

Error index # 0 Full Error Message:
System.data.sqlclient.sqlerror: Login failed for user ISUSR_JAMES, reason not associated with a trusted SQL server connection.

with the following connection string

private string conString = "Initial Catalog=Project;Data Source=192.168.1.51;User ID=IUSR_JAMES;Password=password";

i'm really confused now; i never set a password with the new database user but the code bombs out if its not included.

please help!!

James

 

[obislavu]

The User ID/password you pass in the connection string should be defined only in the database.
Next, be sure the objects (tables, views etc ) that you access from your code using that user have the right permissions , SELECT for example.
But , seems your code reaches the 192.168.1.51 machine and the Project catalog exists but there are problems accessing the SQL server.
Use VS.NET Tools->Connect to database->Data Link Property and here you have Provider tab and Connection tab.
Fill there the info and push the TestConnection button.
You should be able to connect if that machine is reachable, that catalog exist and that user/password is defined in the database.
obislavu

 

[jimbledon]

Hi

The TestConnection button worked okay.
I read up on connections string and if you use sspi it should connect to the windows guest account as default?

private string conString = "Initial Catalog=Project;Data Source=192.168.1.51;Integrated Security=SSPI";

Doing this produced the following error

System.data.sqlclient.sqlerror: SqlError: Login Failed for user ‘James/Guest’

The Windows guest account was turned off; I turned it on and rebooted the machine; still doesn’t connect. It shows the same error.
Correct me if I’m wrong the connection works in the following order?

- Connects to machine (Local JAMES)
- Then on to the account Guest windows account
- Then into SQL Server with the settings of the SQL Server configuration Security and Authentication properties set to ‘Windows only’
So in theory this should work.

 

[chiph]

If you use integrated security (sspi), it will use Windows to authenticate you to the database. This means that your database security must be tied to Windows security. Because of this, there is no need to pass userid & pwd on the connect string, as the authentication will happen automatically.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[jimbledon]

Hi Chip H,

How it is possible to confirm this?

Regards

James

 

[chiph]

How it is possible to confirm this?

From the SqlConnection.ConnectionString documentation:

Name
Integrated Security -or- Trusted_Connection

Default
'false'

Description
When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.
Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[jimbledon]

Hi, I have been away for a while but the problem still exists.

The SSPI for my connection enables auto-authentication, so it should connect okay?

I tested it on a friends machine and it worked fine but for my machine it failed to connect with the following error

System.data.sqlclient.sqlerror: SqlError: Login Failed for user ‘James/Guest’

Is it okay to assume that there is oversight in my configuration where windows security authenticates to the SQL user?
If so how would it be best to debug the connection to the point of failure.

Regards

James