SQL Server - few Tips & Tricks

These days, I’ve been working on a Business Intelligence project, powered by Microsoft Technologies like SQL Server 2012 (both 64 bit and 32 bit versions), Reporting Server, Analysis Server etc. In this blog, I’m going to continually post issues encountered during implementation (mostly deployment, integration and security related), troubleshooting ideas and possible solutions/work-around.

Issues with SQL Server Alias

Aliases are great way to make connection portable as they encapsulate connection details like host, port, protocol etc and provide a connection string ready to be used. However, there are few basic things that need to be taken care of, otherwise, you’ll be troubleshooting your connection problem forever especially in the environment where you have both 32 bit and 64 bit client applications running.

• Aliases are used by client (not server) to establish connection to the server. Confusing? For example in a situation, where your SQLJob being run by SQLAgent on 64 bit SQL Server connects to database hosted on 32 bit SQL server to extract data. In this situation, 64 bit SQL server is considered as a client where as 32 bit SQL server is considered as a server. Similarly, if you are trying to connecto 64 bit SQL server, using SQL Server Management Studio (SSMS), then SSMS is considered as a client, and 64 bit SQL Server is considered as a  server. Since, Aliases are used by client, you need to make sure, the aliases thus created are compatible to respective clients.

• When I say compatible, I mean, if your client is 32 bit, your alias needs to be created as 32 bit alias otherwise needs to be created as 64 bit. If you don’t know whether your client running as 32 bit or 64 bit and you assign not-compatible alias, you may encounter error something like, “... Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: ... Named Pipes Provider: Could not open a connection to SQL Server [53].... Extract ExceptionDetails [29] ... Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. …” The error message may vary depending upon the specific client trying to establish connection to specific server.

• So, how to create compatible alias(es)? You can use “cliconfg.exe” to create the SQL server alias. Just be careful, if you are on 64 bit environment, default “cliconfg.exe” is 64 bit and creates alias for 64 bit client application, so in order to create an alias for 32 bit client like SSMS, you need to use “%windir%\SysWOW64\cliconfg.exe".  Apart from “cliconfg.exe”, you can also use the “SQL Server Configuration Manager” to create an alias. Unlike “cliconfg.exe”, SQL Server Configuration Manager, provides clearly visible and distinct areas to define 32 bit vs 64 bit aliases. See this screen shot (Scr_1.0). 

Scr_1.0

Note: If you are not sure whether your client is 32 bit or 64 bit, you can always create same alias (with same name) for both 64 bit and 32 bit clients.

Issue with DNS  

Just like an alias, DNS (Domain Name System) adds great value in making connection portable. Because, IPs can change, host name can change, but DNS may remain the same letting application to run continuously in the situation of change. You can use DNS while creating an alias in the server name field. However, DNS may not work in a situation if you are trying to connect to local SQL server using DNS locally. You may get an error (seen from SSMS), saying, “Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)". See screen shot Scr_2.0.

Scr_2.0

Details and suggestion on this issue, please see the point #5 of Dan Benediktson’s blog