Reader Challenge: Unencrypted Information

Test your SQL Server Savvy

[Editor's Note: Subscribe to the SQL Server Magazine UPDATE newsletter (http://www .sqlmag.com/resources/main/cfm) to solve next month's SQL Server problem. First-place winners receive $100; second-place winners receive $50.]

AND THE WINNERS ARE . . .

Congratulations to Chip Andrews of Computer Associates, Cumming, Georgia, and Dimiter Vassilev of GLOBAL Consulting Ltd., Sofia, Bulgaria. Chip won first prize of $100 for the best solution to the Reader Challenge. Dimiter won second prize of $50.

Problem

Linda tries to be a security-conscious DBA. To handle permission differences she uses application roles based on which application the users use. She uses Windows NT Authentication and has worked with the company IT security team to ensure that all the NT accounts use the minimum password length and other security features. She's now looking at the data transmitted across the wire. She's worried that someone could use a network sniffer to dig up information. Help Linda avoid passing unencrypted information to and from SQL Server. How can Linda avoid sending information in clear text to and from SQL Server?

Solution

The Multiprotocol netlib (remote procedure call) supports encrypted information. So, Linda first uses the Server Network Utility to edit the Multiprotocol netlib, configuring the Multiprotocol netlib to require encrypted communication. She then removes all other netlibs in the Server Network Utility, which specifies which netlibs SQL Server uses.

Because she can't remove Named Pipes in the Server Network Utility, Linda needs an alternative. She could use the Regedit program to remove the Named Pipes entry in the Registry. But directly editing the Registry generally isn't a good idea. A Microsoft-supported solution is to edit the Named Pipe entry in the Server Network Utility, changing the name of the pipe that SQL Server uses. SQL Server will still use Named Pipes, but the users won't be able to connect through Named Pipes because they won't know the pipe name. (Note that Linda will need to change the pipe name to the original default name during service-pack installations and upgrades.)

Then Linda can use the Client Network Utility on the SQL Server machine to add a server alias with the local machine name and make it use the Multiprotocol netlib. Also, she must make sure that SQL Server Agent can still log on. In Enterprise Manager, she right-clicks SQL Server Agent, chooses Properties, and under SQL Server Alias on the Connection tab, specifies the alias she added.