Manage SQL Server Connections in ADPs - Page 2

Code post mortem

This code is not pretty, but it works.  First, we get a list of available servers using the EnumerateServers() function.  Next, we check the string for specific SQL Servers in the order I want them to be used.  Production if available; my laptop next; then my desktop and finally my server.  If none are found, it defaults to production.  Strangely enough, this is possible since the function to enumerate network servers does not work for Workgroups and returns an empty string unless there is a SQL Server running on the local machine.

Yes, it solved my problem, but I do not really like it that well.  It requires that the code in the application know about potential servers and, in this case, at least, the connection string information is exposed in the code, posing a security risk.  It worked, yes, and I used it, yes, but I kept my eyes open for a better solution ... and I found one, here in the web pages of Database Journal.

Late one evening I was reading over my DBJ newsletter and I saw a reference to a SQL Server article that mentioned the alias function of the SQL Server Client Network Utility. Hmmm!  What if I could create an Alias to my SQL Server with the name of my client's SQL Server?  That would sure make things simple.  The login password could be obfuscated by the ADP, not being exposed in my code and as far as the application is concerned, so long as it finds a server with the correct name and user login, it's a GO for opening up.

Could it really be that simple?  Yes it is.  The two screen shots below demonstrate how easy it is to create an alias server on a machine that is already running SQL Server.  Simply launch the Client Network Utility from the SQL Server program item menu, select the Alias tab and click Add.

You will then be prompted with the following screen.  Enter any alias name and then provide the actual server name (or IP address as shown below).  Select TCP/IP as the protocol and click OK.  That is all there is to it.  Now, my aliased server, RoofSQL, shows up in Query Analyzer as an available SQL Server and when I open my ADP, it finds what it thinks is the correct production server.  Now THAT'S a solution.

 

Conclusion

This has not been the most elegant article I have ever written, though I have mulled over its contents for weeks.  The topic appealed to me because, after searching the newsgroups for an answer, I came up empty and I figure if I am asking this question, someone else may be also.  Add to that the even cleaner solution of creating an alias server and I figure I have something worth writing about. 

» See All Articles by Columnist Danny J. Lesandrini