I recently had an issue where some databases that were created from a script the database user was disabled but in the system it was fine. There is no way from the GUI to enable this. After much searching I found my answer in an MSDN forum (http://social.msdn.microsoft.com/forums/sqlserver/en-US/5abd5b42-cd84-4fcc-9e3e-3dcac8814800/database-users-showing-disabled) where it was mentioned the CONNECT privilege is missing. The command below will allow you to verify this is your issue replace the <username> with your effected username and run using the effected database:
SELECT * FROM sys.database_principals dprinc INNER JOIN sys.database_permissions dperm ON dprinc.principal_id = dperm.grantee_principal_id WHERE dprinc.name = '<username>' AND dperm.permission_name = 'CONNECT'
The following script after replacing the <databasename> with your database name and <username> your effected username will fix this if the permission is missing:
/* Please replace the use <databasename> and the <username> */
USE < databasename >
IF NOT EXISTS (
FROM sys.database_principals dprinc
INNER JOIN sys.database_permissions dperm ON dprinc.principal_id = dperm.grantee_principal_id
WHERE dprinc.NAME = '<username>'
AND dperm.permission_name = 'CONNECT'
Recently I was working on a migration to a SQL Server 2012 environment. After we had created our performance environment where we had created an availability group listener (AGL) I received complaints from my developers that they could not connect. Initially they were issues using the 2008 R2 client which was resolved by asking them to upgrade their client. However they were receiving the following error from their application:
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21033; handshake=310;
I did some testing on my own and was not getting this error even when using a C# console test application. The unfortunate situation here is that this error message is very generic. Some initial investigating (searching Google) I did not find any solutions that applied to this case. Many related to older versions of SQL Server. The solution was finally found as there was an article from Microsoft's knowledge base (http://support.microsoft.com/kb/2870437). The issue is with a TDI driver issue. Applying the hotfix the issue was resolved and I was now able to connect via the connection string that previously did not work.
Part of testing this was using a tool I created (AGLPing) that allows me to test multi-subnet response times. Because this was an exe it makes the solution portable. You can download the code for this tool and use it as you wish via Github: https://github.com/russ960/AGLPing
I had an awesome time today at SQL Saturday 97. My presentation was not with out it's challenges though as my laptop decided to take a crap and die. But thankfully Wes Brown the most super awesome MVP on the planet had a spare laptop and I used it to complete my presentation. I was able to enjoy time with my fellow DBAs and enjoy presentations by the MidnightDBA's Sean and Jen McCown as well as Joe Celko. I'm attaching my presentation for those that could not attend and welcome feedback.
Well I've recently made a commitment to myself to start being a contributor to the SQL Server community to help encourage myself to grow as a database professional. As part of this new commitment I've started this blog and I'm speaking at SQL Saturday #97 here in Austin. I will be speaking on architecting no downtime databases. I hope to see everyone there and look forward to my first speaking engagement. I will update this site with my presentation and I will also publish it on the SQL Saturday site.