Rollback

(Magyarul itt)
Occasionally it happens that someone is unable to log in to SQL Server because they mistyped the password, have no permission, etc. This is not a problem – as long as we know what is the blocking issue. But how about someone being cocksure they’re trying the correct user, password ,server, etc and still failing? The most straightforward solution is my favorite, reading the SQL errorlog, once again. If the server is set to audit failed logins (and this is the default), you can find the error in the errorlog as well, not on the client side only:

Msg 18456, Level 14, State 1, Server DEMOSQL1, Line 1
Login failed for user ‘tygger’

Actually, something better gets in to the log, but first, let’s analyze the first line, that is ,the first three numbers of the error. the first one is the error number, the identifier of the error; the second is the severity, that is, how bad it is. The bigger the number the worse it is. If you see something above 19, you might be in real trouble. The third number is the state, which is an interesting species. This can be used to provide diagnostic information, like throwing an error with state 1 from an SP and with state 2 from parameterized query. It can make DBAs (and developers) life easier. Now back to that errorlog!
Continue reading ‘Error 18456, Level 14, state – SQL Server login errors’ »

Up to SQL 2005, the sysadmin role contains the local Adminstrators group by default (and by design), under the name BUILTIN\Administrators. This bothered some folks as it wasn’t secure enough for them, so the SQL 2008 asks you during the install who should be the member of the sysadmin role, no automatic membership granted to local admins. This is a pretty well-known feature.

However, it it lesser known that if you start the SQL Server in a single user, minimal mode (sqlservr -m -c started from the directory of the program directory, you can see it at the installed service), the local admins become sysadmins, independently of their normal privileges. This can be a failsafe solution, for example in an over-hardened environment where someone accidentally lost the password of the renamed and disabled sa account, which was the only sysadmin. You can reset passwords, add new sysadmins or whatever you want.

(Magyarul itt)

Many people faced the upleasant message below while trying to connect to SQL Server with SQL authentication: “Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection” The most typical reason for this is that the server is configured to allow only Windows logins, not SQL logins. The cure is pretty simple, open up server properties and change the authentication option from Windows only to SQL and Windows – and there you go.

Recently, I met the error message in a different situation. A few guys couldn’t connect to a server with Windows integrated authentication. They got the error message above. Others could connect without any issue. I tried to figure out what was going on: my first candidate was MTU size but as the authentication token size was the same size (actually, it was bigger for those who had no problem), I ruled this out. Then I checked if they could connect to a file share on the machine. This action failed and I found this in the security eventlog:

Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 534
Date: 8/3/2008
Time: 3:04:30 PM
User: NT AUTHORITY\SYSTEM
Computer: SQL10
Description:
Logon Failure:
Reason: The user has not been granted the requested
logon type at this machine
User Name: erik
Domain: MYDOMAIN
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: ERIKPC

So what the heck does this mean? I asked my old pal Google, who said he read about it once, and people say it’s nothing else than network logon failure. So I decided to check the local security policy. And I found that in the User Rights Assignment node the Access this computer from the network privilege was granted only the local Administrators group. At that point I banged my head into the wall why I hadn’t found the pattern sooner and also extended this privilege to the users group as well. After a policy refresh, it worked.

Lookink back, it was pretty simple. As the peons users were not authorized to access the computer via the network, the OS tore and dropped their token so the SQL Server didn’t get anything from their identity. Once again I learnt something new.

A few days ago I found that the replication into a database on one of our SQL 2005 Servers slowed down. The same publisher was replicated to multiple subscribers without any issue. I decided to check the database for blocking processes, because this db was used for reporting:

select * from sysprocesses
where
spid in (select blocked from sysprocesses where spid <> blocked)
and dbid = db_id('MyDatabase')

(The where spid <> blocked clause was needed because from SQL 2000 SP4, the “self-blocking” is also listed as a blocking but it wasn’t interesting in this case.) I expected to spot one of our report writers running a bloated query with massive joins ans sophisticated filters but it wasn’t the case. I found nothing. I had a suspicion that if I restarted the server, this phenomenon would disappear but as the server was still in a usable state, I decided to dig deeper cause I hate mysteries unresolved.

I started to check the distribution agent history in Replication Monitor but the only thing I found was that the agents were very slow. I checked them in the Activity Monitor and noticed that they were spending lot of time in RESOURCE_SEMAPHORE wait state. I checked the excellent white paper about SQL Server wait types, and saw that it usually means that due to high concurrent load, the query should wait for memory grant. So I checked the memory usage a bit.
Continue reading ‘A very slow SQL Server’ »

After getting a new Microsoft failover cluster, I found during the failover tests that the network name failed over horribly slowly. Otherwise everything was fine, but a complete SQL cluster failover took more than a minute and it was all because the network name remained online pending for ages. After some googling I didn’t find any specific solution, but I found a blog post about Exchange 2007 on Windows 2008 which put me into the right direction (that is, answered my question in a sentence) and gave the “I should have thought of this earlier” feeling (which helps me to exercise humbleness).

The sentence is the following:

In previous versions of Windows Cluster Server, every time a Network Name came online, it would register with DNS.

So I just checked my network settings and found that the Register this connection’s addresses in DNS checkbox was ticked. I cleared it and made a few tests: the network name came online in a second or less.

And I hate all the forums outside because you can’t just answer other’s question, you have to register (your login is already in use/too long/too short/contains invalid character/not complex enough/blocked by the swearword filter – and your password is way worse ) – and you get the confirmation mail the next day. So I left a few guys unanswered, but I hope Google will pick up this post so it’ll help on others.