Page 1 of 1
C# Throwing away login user when connecting to DB
Posted: 12 Mar 2009, 12:54
by RuadRauFlessa
I am currently sitting with a very weird problem. I have a Windows service written in C#. Before I make the connection to the SQL server the user details are all there in the connection string but when it connects I get an error back saying that the login has failed for user <null>.
Code: Select all
.
.
.
public System.Data.SqlClient.SqlConnection getSQLConnection(String sServer, String sDatabase, String sUsername, String sPassword)
{
String conStr = "Server=" + sServer + ";Database=" + sDatabase + ";User Id=" + sUsername + ";Password=" + sPassword + ";Trusted_Connection=True; MultipleActiveResultSets=true;";
EventLog.WriteEntry(sSource, "Returning Connection String = " + conStr, EventLogEntryType.Information);
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(conStr);
EventLog.WriteEntry(sSource, "Returning Connection = " + conn.ConnectionString, EventLogEntryType.Information);
return conn;
}
.
.
.
//Later on in the code
_sqlConnection_ = getSQLConnection(Properties.Settings.Default.SQLServer, Properties.Settings.Default.SQLDatabase, Properties.Settings.Default.SQLUser, Properties.Settings.Default.SQLPassword);
_sqlConnection_.Open();
System.Data.SqlClient.SqlCommand comm = _sqlConnection_.CreateCommand();
comm.CommandText = Properties.Settings.Default.SQLQuery;
comm.CommandType = CommandType.Text;
I added the event log entries to see what is going on so don't worry about them. Both of them throw me the correct connection string which looks like the below. Just without all the sensitive stuff.
Code: Select all
"Server=127.0.0.1;Database=stagingtable;User Id=*****;Password=*****;Trusted_Connection=True; MultipleActiveResultSets=true;"
Re: C# Throwing away login user when connecting to DB
Posted: 12 Mar 2009, 13:01
by Ron2K
Try "User ID" and not "User Id" - I believe it may be case sensitive.
Oh, and there is a SqlConnectionStringBuilder class that may make your life easier.
Re: C# Throwing away login user when connecting to DB
Posted: 12 Mar 2009, 13:07
by rustypup
1) the connection string is being flushed out of scope or
2) go with Ron2K's suggestion - it may be a simple parsing error...
Re: C# Throwing away login user when connecting to DB
Posted: 12 Mar 2009, 13:23
by RuadRauFlessa
Something like the below
Code: Select all
public System.Data.SqlClient.SqlConnection getSQLConnection(String sServer, String sDatabase, String sUsername, String sPassword)
{
System.Data.SqlClient.SqlConnectionStringBuilder stringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
stringBuilder.ApplicationName = "DOJ Link Health Check Service";
stringBuilder.DataSource = sServer;
stringBuilder.InitialCatalog = sDatabase;
stringBuilder.MultipleActiveResultSets = true;
stringBuilder.Password = sPassword;
stringBuilder.UserID = sUsername;
EventLog.WriteEntry(sSource, "Returning Connection String = " + stringBuilder.ConnectionString, EventLogEntryType.Information);
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(stringBuilder.ConnectionString);
return conn;
}
Re: C# Throwing away login user when connecting to DB
Posted: 12 Mar 2009, 13:38
by Ron2K
Yeah, that should do it.
Re: C# Throwing away login user when connecting to DB
Posted: 12 Mar 2009, 13:42
by RuadRauFlessa
Thanks it did the trick. Now to sort out the only other problem with the service then it is another project completed
Re: C# Throwing away login user when connecting to DB
Posted: 28 Mar 2010, 01:21
by 1gn1t0r
Does Visual Studio Express have support for services ?
Re: C# Throwing away login user when connecting to DB
Posted: 28 Mar 2010, 10:09
by Stuart
Please check the date on threads before you reply to something that has probably been solved a long time ago already.