Archive for the ‘English’ Category.

T-SQL Tuesday #12 – Why are DBA skills necessary?

t-sql tuesdayOne of the few persons I admire for their skills is Paul Randal, who’s the host of the 12th T-SQL Tuesday. T-SQL Tuesday is about blogging about the same topic, inevitably from different angles as we people are different. It’s fun for reading, I hope it’s fun for writing as well – this is my first time participating. Given my current state of mind, this is going to be a rambling, rather than an article.

The question outlined in the title can be interpreted in many ways. The typical things in my head are the following:

  • Programming: set-based, declarative approach vs sequential, procedural programming
  • Operations: RDBMS is quite different from OS and other standard apps (including server products)

Continue reading ‘T-SQL Tuesday #12 – Why are DBA skills necessary?’ »

Error 18456, Level 14, state – SQL Server login errors

(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’ »

SQL Server and forgotten sysadmin passwords

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.

SQL authentication and strict computer policy

(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 very slow SQL Server

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’ »