Inspired by common emails and questions I see, I figured I’d do a series of blog posts on common mistakes folks make with SQL Server called Monday Morning Mistakes (or #sqlM3 for short, since we all love quick hashtags these days). These are meant as quick fixes, nothing too comprehensive. Also since I just made up a hashtag, feel free to share your own #sqlM3 tips on Twitter anytime! Without further ado…
Today’s quick issue: Can connect to SQL Server locally but can’t connect from other server or computer.
Quick answer: Remote connections (read also: any connections that are not local) to SQL Server are disabled by default. This behavior is default in SQL Server 2005 and higher. You have to manually enable TCP/IP protocol to instance to allow connectivity. This requires a service restart to take effect.
1. Open Connection Manager, go to SQL Server Network Configuration.
2. Select the Protocols for the instance you’re wishing to allow remote connectivity.
3. Enable TCP/IP by right-clicking it and selecting Enable.
4. Restart the SQL Server service.
Additional tips: If you continue to have access denied problems don’t forget to check the SQL Server error log for clues as to why connection isn’t working (i.e. incorrect password error). Also check to see if firewall might be affecting connectivity.
4 replies on “Monday Morning Mistakes: Remote Connectivity to SQL Server”
Love the new hashtag theme and great tip as well Jorge. I’ll be thinking about a few to add.
Thanks and yup, feel free to add your own. Would be cool to see everyone’s most common issues.
Really enjoyed this article. Really Great.
Thanks again for the article.Really thank you! Fantastic.