Unable to get SQLConnection to Open() in BackgroundWorker

[Guest]

Hello,

In my ASP.Net app I'm launching a BackgroundWorker thread in my Page_Load
function. In that thread I'm attempting to connect to a SQL server using
this connection string "Initial Catalog=MyDB;Data Source=MyServer;Integrated
Security=True;".

When I use the same connection string in my main thread of execution it
works fine. However when I use that connection string in the newly created
thread I get this exception: "Login failed for user ''. The user is not
associated with a trusted SQL Server connection."

The '' (empty quotes) make me think I need to tell the new thread which user
credentials to use. Is that correct? If so, how do I do it? If not, what
do I need to do?

Thanks!

 

[Aidy]

"Initial Catalog=MyDB;Data Source=MyServer;uid=username;pwd=password;"

 

[Guest]

Aidy,

Thanks for the reply. I need to use Windows Authentication. Your
connection string assumes SQL Authentication.

Any idea why my thread would not know my credentials?

 

[Juan T. Llibre]

See valid connection string variations at :

http://www.connectionstrings.com/?carrier=sqlserver

It looks like you want to use SqlConnection ( .NET ).
Check the SqlConnection (.NET) section and pick one.

 

[Guest]

Thanks for your response. However, that article doesn't address my issue. I
need to understand why my thread doesn't have the same credentials as my
process.

 

[Juan T. Llibre]

re:

I need to understand why my thread doesn't have the same credentials as my process.

Simple...

You are connecting using a Trusted Connection, but are not including
"Integrated Security=SSPI" nor "Trusted_Connection=Yes" in your connection string

The connection string used with Windows authentication must include either the
Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI,
as shown in "How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0"

http://msdn2.microsoft.com/en-us/library/ms998292.aspx

 

[Guest]

That doesn't work either. Keep in mind that the connection string in my
original posting works fine while I'm in the primary thread of execution.
It's only in the new thread that it fails. When I change to "Integrated
Security=SSPI" again it works in my primary thread but not the
backgroundWorker thread.

 

[Aidy]

I haven't used .net 2.0 but I image the backgroundworker thread might be
running in its own process somewhere, or a special process space that is
outside of IIS, so it is not using the same credentials as your asp.net code
is using.

As I said, that's just a guess. Look at the docs to see if you can specify
credentials to the background thread, or find out the credentials of the
thread it is using and add those as a trusted SQL member. Failing that,
create a SQL user just for that process and pass the uid and pwd with the
connect string.

 

[Juan T. Llibre]

Hi, Tom.

Have you tried posting your question in the SQL Server Security forum ?

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

Microsoft SQL Server staffers man that forum
and may have the specialized knowledge you need.

Here's a list of 70 threads which mention BackgroundWorker in the SQL Forums:
http://forums.microsoft.com/MSDN/Se...2,158,227,385,87,92&CSVUserID=&localechoice=9

You might want to give them a glance and see
if others have posted something which might help you.

Other than that, opening a Support ticket may be your last resort.

 

[Guest]

That doesn't work either. Keep in mind that the connection string in my
original posting works fine while I'm in the primary thread of execution.
It's only in the new thread that it fails. When I change to "Integrated
Security=SSPI" again it works in my primary thread but not the
backgroundWorker thread.

Tom-

No idea if this would work for you, but it's a path I just mowed out for
myself:

I was not able to fork the BackgroundWorker from my code. I'm in a VB
module in a 2.0 web app, but the code is not part of an aspx page. I got an
exception on the line RunWorkerAsync(): "Asynch not allowed here, page must
have async attribute set to true." While I was surfing for a way to set the
"page attribute" in a non-page module, I came across this article:

http://www.beansoftware.com/ASP.NET-Tutorials/Asynchronous-Execution-Pattern.aspx

from which, I created my own version of a background worker thread (below).
Not sure how safe/sound my approach is, and would love any feedback on it,
but I am accessing multiple DB's from the low priority thread.

Public Class Test
Public Delegate Sub LongTimeTask_Delegate(ByVal s As String)

Public Shared Sub makeBGW()
'Dim w As BackgroundWorker = New BackgroundWorker()
'AddHandler w.DoWork, New DoWorkEventHandler(AddressOf OnWork)
'w.RunWorkerAsync() 'chokes here
Dim d As LongTimeTask_Delegate = New LongTimeTask_Delegate(AddressOf
LongTimeTask)
Dim r As IAsyncResult = d.BeginInvoke("abcdef", Nothing, Nothing)
End Sub

Public Shared Sub LongTimeTask(ByVal s As String)
Thread.CurrentThread.Priority = ThreadPriority.Lowest
'doing background junk right here, including access to multiple DB's
End Sub

etc..

I do conflict with this guy's rule #19 (changing a thread's priority), but
wasn't sure what else to do here.
http://www.feedshow.com/show_items-feed=bcc15f3d7b8b54935676e00d301b57da

Hope it helps. Sorry so long.