Failed DTS Package locks user out of SQL2005 (Windows Authentication) ... for good!

Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.

In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.

Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.

The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).

We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).

We have been able to replicate this consistently - every annoying time we've tried it!

(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).

Are you saying that you can't connect to Integration Services? From your post the Database Engine is fine, and 'locks user out' is misleading as the account is not locked out.

What does the package try or fail to do (and which step)?
What appears in the application event log when you fail to connect to IS?
Is IS service still running?|||

Thanks for your reply. Allow me to attempt to answer your queries.

Both Integration Services and Database Engine (MSSQLSERVER) are still running. As stated, we can connect to the Database Engine using SQL Authentication, but Windows Authentication (no matter what account) no longer works to either the Database Engine or Integration Services.

OK, perhaps my terminology is not quite correct - it's not an account lock-out, but that's pretty much the symptom. Perhaps I should say "Windows Authentication is broken"?

Sample Event Log (descending timestamp order ie. same as in event log viewer):

MSSQLSERVER Failure Audit (4) 18456 SERVER\Administrator Login failed for user 'SERVER\Administrator'. [CLIENT: <local machine>]

SQLISPackage Error None 12291 SERVER\Administrator SERVER "Package ""DTS_TEST"" failed."

SQLISPackage Error None 12291 SERVER\Administrator SERVER "Package ""ShellPackage"" failed."

SQLISPackage Information None 12288 SERVER\Administrator SERVER "Package ""ShellPackage"" started."

SQLISPackage Information None 12288 SERVER\Administrator SERVER "Package ""DTS_TEST"" started."

At this stage I can't determine which step in the package is broken, but that's not my biggest issue at this point. Why Windows Authentication "breaks", and how to fix it is my real concern.

Extracting RDL file from published reports in RS 2000

In order to have a backup of the rdl files for reporting, does exist a manner
to extract the rdl files inside RS starting from published reports?
In other terms, is it possible to revert the report publishing process in
order to have the rdl files?
Many thanksOn Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
wrote:
> In order to have a backup of the rdl files for reporting, does exist a manner
> to extract the rdl files inside RS starting from published reports?
> In other terms, is it possible to revert the report publishing process in
> order to have the rdl files?
> Many thanks
Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62|||Oh, well! It's very useful.
Many thanks
"Tim Toennies" wrote:
> On Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
> wrote:
> > In order to have a backup of the rdl files for reporting, does exist a manner
> > to extract the rdl files inside RS starting from published reports?
> > In other terms, is it possible to revert the report publishing process in
> > order to have the rdl files?
> > Many thanks
> Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62
>

Extracting data to CSV file

How do you write a query in Query Analyzer that extracts data from a table
into a .CSV file. I need the data in that format in order to upload into an
old Sybase database. ThanksEither use BCP, or select the Query / Results to File option in QA, or
use DTS, or use OSQL with the output switch option. In principle you
can do these things from a script in QA but usually that's not
necessary. If you want to automate it then use SQL Agent or some other
process to invoke one of those methods.
David Portas
SQL Server MVP
--|||SELECT ColA + ',' + ColB + ',' + ColC FROM TableName
Or
Use DTS to export the table to a CSV file
Or
Use DTS to export the file directly to Sybase.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisB" wrote:

> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into a
n
> old Sybase database. Thanks|||Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SMyServ -Uuser -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SMyServ -Uuser -Ppass'
"ChrisB" <ChrisB@.discussions.microsoft.com> wrote in message
news:923AF22E-DF46-4DDB-84BE-18FCAEBE09FF@.microsoft.com...
> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into
an
> old Sybase database. Thanks|||The query requires nothing special. Just execute the query within Query
Analyzer with the results going to the grid. When you get the results back,
click the mouse in the grid and choose the menu option File.. Save As..
"ChrisB" <ChrisB@.discussions.microsoft.com> wrote in message
news:923AF22E-DF46-4DDB-84BE-18FCAEBE09FF@.microsoft.com...
> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into
> an
> old Sybase database. Thanks