C# Throwing away login user when connecting to DB

Get help on programming - C++, Java, Delphi, etc.
Locked
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

C# Throwing away login user when connecting to DB

Post 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;"
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: C# Throwing away login user when connecting to DB

Post 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. :wink:
Kia kaha, Kia māia, Kia manawanui.
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: C# Throwing away login user when connecting to DB

Post 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...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: C# Throwing away login user when connecting to DB

Post 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;
}
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: C# Throwing away login user when connecting to DB

Post by Ron2K »

^^ Yeah, that should do it. :wink:
Kia kaha, Kia māia, Kia manawanui.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: C# Throwing away login user when connecting to DB

Post by RuadRauFlessa »

Thanks it did the trick. Now to sort out the only other problem with the service then it is another project completed :D
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
1gn1t0r
Registered User
Posts: 56
Joined: 07 Feb 2006, 02:00
Contact:

Re: C# Throwing away login user when connecting to DB

Post by 1gn1t0r »

Does Visual Studio Express have support for services ?
User avatar
Stuart
Lead Forum Administrator
Posts: 38503
Joined: 19 May 2005, 02:00
Location: Home

Re: C# Throwing away login user when connecting to DB

Post by Stuart »

Please check the date on threads before you reply to something that has probably been solved a long time ago already.
Image
Locked