Search

Monday, July 22, 2013

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Today when I was connecting my SQL server from some client system, connection established successfully with the server. However, then an error occurred during the pre-login handshake. Following is the detailed error i am receiving. 

(provider: SSL Provider, error: 0 - The wait operation timed out.)
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.


Then I check that TCP/IP protocol is not enabled for clients. Without TCP/IP protocol you can not access the sql server from other clients. Following is the method to enable the protocol.

Go to the Start-->Programs-->SQL Server--> Configuration Tools-->SQL Configuration Manager.

Expand the SQL Network Configuration and click on the PROTOCOLS node

Right click on TCP/IP and open up the PROPERTIES panel

Select the IP ADDRESS tab

Make sure the values for the IP ADDRESS fields are correct and match the system it is running on. If its not the same then provide the valid ip through which you want to access sql server from client machine.

Restart the service, and I I can connect to SQL Server from client.

Monday, July 8, 2013

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

Some times we may get the below error while updating the login from dbo to some other login from user mapping window.

1. I have database which is mapped with dbo login and dbo schema. 
2. I tried to update the database with app_user login, then it displays below error:


Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

To fix this issue we need to change the default owner to 'sa' and then tried to update the login. 

Use database_name
sp_changedbowner 'sa'

Monday, July 1, 2013

Get Login Session count, Session Mode, User Name, Machine name

sys.dm_exec_sessions SP stores the login information about users like username, login time, machine name, authentication mode (Windows or SQL).Therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc. Use below query to get the result:


SELECT 'Authentication Method'=(CASE WHEN nt_user_name IS not null THEN 'Windows Authentication' ELSE 'SQL Authentication' END),
login_name AS 'Login Name',
ISNULL(nt_user_name,'N/A') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count',
host_name As 'Host'
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_name,host_name