Saturday, September 29, 2012

Database Mail has Locked the Domain Account

This was an incident happened to one of my colleague and I was part of troubleshooting team.   Winking smile

He was complaining that his domain account getting locked daily. Their domain has security configuration,

1. Password is expiring quarterly, every three months.

2. Three continues invalid attempts to login will lock the domain account.

Since I had the same issue before, I thought it might be a service. Once I have configured SQL Server  Express service account to login from my credential. Though I am not using this Express instance it has locked my domain account.

So I checked all the services but it went in vain.

We thought it is something to do with Outlook settings, which we reinstalled again didn’t work.

Then we stopped guessing and thought to approaching it more planned way and we started to examine the Security Log. (You would asks, “Why the hell you didn’t do that before” Angry smile)

Well, we found a clue there and it says logon failures for database mail.

Then examine the database mail configuration, yeap! we got it. SMTP authentication was set with Basic authentication instead he could have done with Windows Authentication.

image

After configuring database mail later his password has expired but since he has used basic configuration, still database mail was trying with the previous password. In his database server, there is job to send notification using database mail. with this job database mail getting executed and with that domain account is getting locked.

Thursday, September 27, 2012

Wednesday, September 26, 2012

Friday, September 21, 2012

Largest SQL Server Installations in the World

I was watching the 24hrsofPASS yesterday and came across the Kevin Cox presentation on PASS - What are the Largest SQL Server Projects in the World?

Following are the statistics for SQL Server world wide.

image

Following are most valued SQL Server clients.

image

Here are some statistics for bwin.

image

Sunday, September 16, 2012

Internal Version of SQL Server Databases

SQL Server has an internal version for it’s databases which as users you don’t need to know about them. But, in some cases like, attaching databases and restoring them between different version you will end up with error messages indicating these internal version numbers. So you need to know about these version numbers.

Following is the error message I received, trying to attach database which was built in SQL Server 2012 and surprisingly my target server is also SQL Server 2012.

image 

It says my current SQL Server version is 705 while I am trying to attach a database of version 706. But both are SQL Server 2012.

Though both are SQL Server 2012, 705 stand for SQL Server 2012 RC0 while 706 stand for SQL Server 2012 RTM!

Here are the internal versions of different SQL Server versions.

image

This blog post describes about how to measure the version number and other aspects of it.

Thursday, September 6, 2012

Sunday, September 2, 2012

Query Plan Mysteries

Found nice article about query plan. In summery, query plan is cached not only with the queries but with the SET options. so if your query got cached with a ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER and if you are running a acessing it with different set option that previously cached plan will not be used.

Also, depending on your application, DEFAULT set option will be differed,

image

Specially ARITHABORT setting is ON for SSMS while for ADO.Net it is OFF!.

Read the article, you will find it interesting.