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