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.
