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.

Extremely slow client connection to MSSQL

Hallo

i'm experiencing an extremely slow connection from a WXPP Sp2 client to a MSSQL2000 running on a W2k server. The client is running a VB6 application that connect with Windows authentication: every form requesting data opens with a long delay at the first launch; next attempts run normally fast.

In the same LAN there are some others identical clients, all running fine.

Every other network activity from that client is ok.

Where should i start to investigate from?

Can you check how much RAM is on this machine compared to the other machines.

The reason I say this is that if its slow the first time and quick thereafter, then its as if the machine is busy loading the first time, and as it has it memory thereafter, quick any subsequent times.

Regards,

Barry Andrew

|||

From what you described, looks like yourissue only happens between one machine and the server on you local network. There are several things that are normally helpful.

(1) "ping" to see the round trip latency between these two machines and compare it with others. Try multiple times. You can also try to copy files between machines and count the elapse time. If there is big discrepancies, there is network issues, you need to contact your admin to resolve it.

(2) Use provider prefixed connection string. For exampl, osql -E -Stcp:servername,portnunber. Explicit connection string can avoid the overhead of trying multiple different providers during connection and some of them are not supported by your server/network.

(3) If the delay happens after the connection, you can use SQL profiler. But according to your description, I think your problem is at connection stage.

HTH.

Extremely High Lock Timeouts

I have recently been investigating some server issues and
found that at certain times of the day we our
experiencing lock timeouts in 10,000 per second. Our
lock timeout value is set to -1, which to me means that
the locks should never timeout.
We are not experiencing an deadlocks during these times
and all lock timeouts are key locks.
Can anyone clear this up for me.There are lots of internal synchronization protocols which request locks
with a 0 length timeout. These are refered to as "NoWait" locks. That is, if
SQL Server can get the lock now, great, otherwise if it can't get the lock
it'll do something different. Unfortunately, these show up in profiler as
lock timeouts when they're really just part of the normal internal locking
protocols. (SQL Server has no way of knowing at the profiler level).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeremy" <anonymous@.discussions.microsoft.com> wrote in message
news:1ce5d01c4532b$c5ea1cd0$a101280a@.phx.gbl...
> I have recently been investigating some server issues and
> found that at certain times of the day we our
> experiencing lock timeouts in 10,000 per second. Our
> lock timeout value is set to -1, which to me means that
> the locks should never timeout.
> We are not experiencing an deadlocks during these times
> and all lock timeouts are key locks.
> Can anyone clear this up for me.