SQL SERVER CONNECTIVITY ISSUES

We have been seeing and trying to resolve SQL connectivity issue all the time, I remember I started using SQL from 7.5, but not much then. I really dug in from SQL 2000. I guess it would be helpful if we can put some guidance on how to resolve connectivity issues. So, here comes a proposal based on my experience and some study. Basically, when you failed to connect to your SQL Server, the issue could be:


1) Network issue
2) SQL Server configuration issue
3) Firewall issue
4) Client driver issue
5) Application configuration issue
6) Authentication and logon issue

Usually, customers see connectivity issue in their applications, but it would be great if you can follow the SQL MSDN Forum steps below to eliminate issues one by one and post a question on SQL forum in MSDN (http://msdn.microsoft.com) if needed.

Step 1: Network Issue
You might be able to make local connection without a working network, but that's a special case. For remote connection, a stable network is required. The first thing to trouble shoot SQL connectivity issues is to make sure the network we rely on is workable and stable. Please run the following commands:

ping -a     (use -4 and -6 for IPv4 and IPv6 specifically)

 nslookup (type your local and remote machine name and IP address multiple times)

Be careful to see any mismatch on the returned results. If you are not able to ping your target machine, it has high chances that either the network is broken or the target machine is not running. It's also possible the target machine is behind a firewall and the firewall blocks the packets sent by the ping command, though. Windows firewall does not block ping (ECHO) packet by default, but you may have to check the ICMP page to see if echo request is allowed. The correctness of DNS configuration on the network is vital to SQL connection. Wrong DNS entry could cause all sorts of connectivity issue later.

Step 2: SQL Server Configuration Issue
You need to make sure the target SQL Server is running and is listening on appropriate protocols. You can use SQL Server Configuration Manager (SCM) to enable protocols on the server machine. SQL Server supports Shared Memory, Named Pipes, and TCP protocols (and VIA which needs special hardware and is rarely used). For remote connection, NP and/or TCP protocols must be enabled. Once you enabled protocols in SCM, please make sure restart the SQL Server.You can open errorlog file to see if the server is successfully listening on any of the protocol. The location of errorlog file is usually under:

%ProgramFile%Microsoft SQL Server/MSSQLxx.xxx/MSSQL/Log (this path also depends on how you have split your data files and log files path)

If the target SQL instance is a named instance, you also need to make sure SQL Browser is running on the target machine. If you are not able to access the remote SQL Server, please ask your admin to make sure all these happen.
























Step 3: Firewall Issue
A firewall (either operating system based or antivirus based) on the SQL Server machine (or anywhere between client and server) could block SQL connection request. An easy way to isolate if this is a firewall issue is to turn off firewall for a short time if you can. Long term solution is to put exception for SQL Server and SQL Browser.

For NP (Named Pipe) protocol, please make sure file sharing is in firewall exception list. Both file sharing and NP use SMB protocol underneath.

For TCP protocol, you need put the TCP port on which the SQL Server listens on into exception.

For SQL Browser, please put UDP port 1434 into exception.

Meanwhile, you can put sqlservr.exe and sqlbrowser.exe into exception as well, but this is not recommended. IPSec between machines that are not trusted could also block some packets. Note that firewall should never be an issue for local connections.

Step 4: Client Driver Issue

At this stage, you can test your connection using some tools. The tests need to be done on client machine for sure.
First try:
Telnet
You should be able to telnet to the SQL server TCP port if TCP is enabled. Otherwise, go back to check steps 1-3. Then, use OSQL, SQLCMD, and SQL Management Studio to test sql connections. If you don't have those tools, please download SQL Express from Microsoft and you can get those tools for free.

OSQL (the one shipped with SQL Server 2000) uses MDAC.
OSQL (the one shipped with SQL Server 2005 & 2008) uses SNAC ODBC.
SQLCMD (shipped with SQL Server 2005 & 2008) uses SNAC OLEDB.
SQL Management Studio (shipped with SQL Server 2005 & 2008) uses SQLClient.
Possilbe command use:

osql -E -S

osql -U -S

SQLCMD also applies here. In addition, you can use -Stcp:Your_target_machine, Tcp_port
For TCP,  -Snp:Your_target_machine\Your_instance 
For NP,    -Slpc:Your_target_machine\Your_instance for Shared Memory. 

You would know if it fails for all protocols or just some specific protocols.

At this stage, you should not see general error message such as error 26 and error 40 anymore. If you are using NP and you still see error 40 (Named Pipes Provider: Could not open a connection to SQL Server), please try the following steps:

a)Open a file share on your server machine.
b)Run   net view \\your_target_machine and
            net use \\your_target_machine\your_share  
(You can try Map Network Drive from Windows Explorer as well)

If you get failure in b), it's very likely you have operating system/network configuration issue, which is not SQL Server specific. Please search on internet to resolve this issue first.You can try connection using both Windows Authentication and SQL Authentication. If the tests with all tools failed, there is a good chance that steps 1-3 were not set correctly, unless the failure is logon-related then you can look at step 6 below.
If you succeeds with some of the tools, but fails with other tools, it's probably a driver issue. You can post a question on the MSDN forum and give us the details.
You can also use “\windows\system32\odbcad32.exe” (which ships with Windows) to test connection by adding new DSN for various drivers, but that's for ODBC only.


Step 5: Application Issue
If you succeed with steps 1-4 but still see failure in your application, it's likely a configuration issue in your application. Think about a couple of possible issues here.
a) Is your application running under the same account with the account you did tests in step 4? If not, you might want to try testing in step 4 under that account or change to a workable service account for your application if possible.
b) Which SQL driver does your app use?
c) What's your connection string? Is the connection string compatible to your driver? Please check http://www.connectionstrings.com/ for reference.


Step 6: Authentication And Logon Issue
This is probably the most difficult part for sql connectivity issues. It's often related to the configuration on your network, your OS and your SQL Server database. There is no simple solution for this, and we have to solve it case by case. There are already several blogs in sql_protocols talking about some special cases and you can check them to see if any of them applies to your case. Apart from that, things to keep in mind include:
a) If you use SQL authentication, mixed authentication must be enabled. Check this page for reference http://msdn.microsoft.com/en-us/library/ms188670.aspx
b) Make sure your login account has access permission on the database you used during login ("Initial Catalog" in OLEDB).
c) Check the eventlog on your system see if there is more information


And that wraps it up.

WHAT IS WSS?...WHAT IS SHAREPOINT?

Have you ever been confused? I mean confused in the sense of taking a decision to do something or not. Well I am right now. Punching these keys on the keyboard right now and I'm wondering if I should just stop...stretch and get out of here...I mean get out of this blog post...stop trying to ramble on Sharing the Point (as one of my office friend puts it).

You don't want to blog on SharePoint? Cool...who cares? That's exactly the point. No one cares, right? Yeah, it's that easy to say truly. I didn't care once before but now I do. The funny thing with SharePoint is that it looks like it isn't popular, it isn't known, no one is using it and all of that...THEN...BOOOOOM!...in your face, you check with the major multinationals (oil, finance, telecoms, automobile, health, name it) and they are using it like crazy!...and you're like you want to take your words back. That's how crazy SharePoint is. It is one of Microsoft's most subtle product, a whole new world of logic and programming altogether.

Another "shadaBANG" (explosive) of SharePoint is it's comparative ease of use and surprises. I mean you want to do something and you get into a "what-da heck" kind of situation. As a professional and as all professionals do (you know what I mean right?...the click...click...clicks and no way out stuff), then you throw your hands in the air and ..."phew!"...google it out, the community (technet, msdn, techrepublic, and all kinds of forums) is your place of call. This is where you meet the next BANG tons of them out there!!!

This the reason I didn't want to blog on SharePoint. I feel we have enough out there. BUT THEN...sometimes when a problem presents itself, you find out that you don't have all the time in the world to solve it, you are fighting against time;you've got all these people, clients, customers, users who are breathing down your neck, sometimes asking for close-to-impossible stuffs. If you are lucky to have an organized environment where SLAs and ticketing are logged...hold on a minute...did I just say if you are lucky to be in an organized environment? Well that might as well be unlucky!...then you have to deliver. That's when someone's blog or forum saves your day with a solution or something close to it that you work with. I've been there and I know what it feels like.

That's why I am blogging on SharePoint Espada right now. I will definitely be helping someone out there who will soon enough find himself in where I have been before. So, for the sake of the community of professionals...and also not to get rusty...I will blog on SharePoint.

The blog name? Well I could have used SharePoint Arrancar or SharePoint Zampaktou...y'know... something very Ichigo-ic, or Bleach-like (I am a Bleach fan...no apologies!)...but I had to settle for the Spanish word for sword (espada).

Let's see where we can get started.

Microsoft Site Server, first released in 1996, was Microsoft's solution to the growing difficulty of managing complex websites which included multiple technologies, such as user management and authentication/authorization, content management, analysis, indexing and search. Site Server 2.0, released in early 1997, incorporated electronic commerce technology from Microsoft Merchant Server, Microsoft's first effort at providing a solution to the growing business of Internet-based commerce (or e-commerce). During the course of its evolution (culminating with Site Server 3.0), Site Server expanded on Merchant Server's functionality by annexing content management tools; which would typically be involved, it was thought, in facilitating the management of Web-facing content. Consequently, Site Server became not only a solution for businesses wanting to sell products online, but companies who had corporate intranet servers hosting documents. Site Server felt less like a product, more a collection of tools that didn't have a home any where else. At the time, there weren't too many servers to choose from. King of the range was Exchange Server (version 5.5), the messaging platform. SQL Server 6.5 was paddling around in the nursery pool.

Although Site Server went through several iterations by mid-2000, the portal market was taking centre stage Windows Server 2000 (upgrade from NT 4.0, introducing Active Directory) and SQL Server 2000 (upgrade from SQL Server 6.5). Exchange Server 2000 was completed and also released. Early in 2001, SPS 2001 was finally released. Having started life as a document management and indexing application, its new focus was on targeting the growing portal market. Whilst its features were basically good, it was saddled with two major problems - the web store and the digital dashboard. 2001 Microsoft acquired content management vendor nCompass, and re-branded the product Content Management Server 2001 (CMS 2001). Initially the product was targeted with providing CMS capabilities for Commerce Server (re-completing the feature set that existed back in Site Server days). However, as the portal market continued to grow and overlap with the existing web content management market, CMS 2001 began to compete with SPS 2001. And to further confuse customers, Microsoft also released a free add-on to Office 2000 called SharePoint Team Services (STS) that provided web-based team collaboration features. Confused? Plenty of customers were.

Development options for the next version of SharePoint were relatively simple - replace the Web Store with SQL Server as the storage back-end, and replace Digital Dasboard with ASP.NET for the front-end. As always, the devil was in the details - easy choices don't necessarily lead to easy development. The focus was on improving scalability and improving portal features and that meant some of the document management features were going to struggle to be included. In October 2003, Microsoft released a new version of Office - Office 2003 - and included the new upgraded SharePoint range within the Office brand. STS was renamed Windows SharePoint Services (WSS), and became part of Windows Server 2003. It provided a collaboration store and a web part user interface built using ASP.NET. SPS v2 was built on top of WSS and named Microsoft Office SharePoint Portal Server 2003 (SPS 2003). SPS contained indexing/search, personalisation and enhanced management/taxonomy. So we now had a portal product with a bit more scale than its predecessor that used Microsoft's common developer tools (well, pretty much). Microsoft began to creep up Gartner's magic quadrant for portals, and all would have been well if it hadn't been for Enron and WorldCom...

Just as SharePoint moved away from document management and focused on portal capabilities, Sarbannes-Oxley was born and, all of a sudden, document and records management moved back up the agenda. Simultaneously, the continued growth of the portal market made it clear that portals and web content management were on a collision course, with document and records management joining the party. Windows SharePoint Services 2.0 was marketed as an upgrade to SharePoint Team Services, but was in fact a completely redesigned application.

Windows SharePoint Services 3.0 was released on November 16, 2006 as part of the Microsoft Office 2007 suite and Windows Server 2003. WSS 3.0 is built using .NET Framework 2.0 and .NET Framework 3.0 Windows Workflow Foundation to add workflow capabilities to the basic suite. By the beginning of 2007 WSS 3.0 was made available to the public. Windows 2000 Server is not supported by WSS 3.0.

WSS version 3 marked a significant maturation of the product. Version 3 supported more features commonly used in Web 2.0 solutions like Blogs, Wikis and RSS feeds. Microsoft has changed the name beginning with version 4.0 to SharePoint Foundation 2010.