Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For troubleshooting purposes, I would like to be able to check if a client can connect to a SQL Server instance, independent of the application that possibly can't connect to the SQL Server.

Is there an easy way (that means, not having to install 3rd party software) to do this using the default Windows system tools? Perhaps using scripts or network applications?

share|improve this question
    
on my notebook i can create a sqlserver odbc connection and test access to the sqlserver. But I don't no if the sqlserver odc driver can be found on all clients that are configured to access sqlserver. but if it could be found this would be the next test after checking the reachiility of the server's sqlserver prot with telnet. –  miracle173 Sep 6 '12 at 14:21

2 Answers 2

up vote 5 down vote accepted

If the server is using TCP/IP, then the simple way is to just telnet to the SQL Server port and see if it connects. By default, that's port 1433, so this should work:

telnet servername 1433

That will probably be appropriate in most cases.

If it's using a different port, or dynamic ports (common with a named instance), then you'll need to determine which port it's currently listening on. Check SQL Server configuration manager to see if it's a specific port, or dynamic ports. If it's using dynamic ports, then as long as you don't have multiple instances on the server, netstat -abn is probably the simplest way to find what it's using. Otherwise, dig through the Windows event log or the SQL Server error log for a message indicating which port is in use by the instance.

If SQL Server is using Named Pipes, then I believe if you're able to access shares on the machine, you have adequate network connectivity. This article says you can go further and try connecting to the IPC$ share:

http://msdn.microsoft.com/en-us/library/aa275787%28v=sql.80%29.aspx

net use \\servername\IPC$

That's written for SQL Server 2000, but I don't imagine this aspect has changed much, if at all.

share|improve this answer
1  
That's not seeing if a client can "connect to a SQL Server instance". That's just testing to see if a port is listening. –  Thomas Stringer Sep 5 '12 at 12:40
    
...which is seeing if a client system "can connect to a SQL Server instance, independent of the application that possibly can't connect to the SQL Server." Anything beyond the network connectivity is dealing with application issues. –  db2 Sep 5 '12 at 12:54

Provided you have the Microsoft.SqlServer.Smo assembly in your GAC on the local machine, this can be easily done with PowerShell:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

$server = New-Object Microsoft.SqlServer.Management.Smo.Server("YourSqlServerName")

# do a simple operation to see if you can get data
Try {
    $server.Version | Out-Null
    Write-Host "SQL Server connection successful!!!"
}
Catch [System.Exception] {
    Write-Host "Error connecting to SQL Server..."
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.