Default Database brought offline SQL Server Agent Job fails!!!

Hi All,

There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.

I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.

Any pointers ?

Have you tried tracing the connection attempt using SQL Profiler?

This is a SQL engine security issue - its nothing to do with SSIS.

-Jamie

|||

Jamie,

You were right this has nothing to do with SSIS indded, I was using a Proxy account and I tried resetting the password, and it seems to work now. Thanks in helping me look at the right area.

Debugging and ActiveX Script Task in a DTS Package running on SQL Server 2005

I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.

I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?

Jay Abbott

I have not tried, but another way to stop execution is to use a MsgBox. Whilst it is paused, you could attach the debugger to the DTS process.

I would bin the ActiveX Script, or leave it in DTS. The diagnostic information is absolutely non-existent coming out of the SSIS ActiveX Script Task, and for my money that is unacceptable in a system. Leaving it in DTS is quite easy, and perhaps call the DTS from SSIS to allow you to migrate some functions in your process. Just the fact that no error information is forthcomming in the event of a failure is enough for me to avoid using it entirely.