Disabling Protocols

Congratulations to Marcos Kirchner, a student at the University of Blumenau (FURB) in Santa Catarina, Brazil, and Nico De Greef, a software architect for Denco in Belgium. Marcos won first prize of $100 for the best solution to the July Reader Challenge, "Disabling Protocols." Nico won second prize of $50. Here’s a recap of the problem and the solution to the July Reader Challenge.

Problem:

Sean is a systems administrator in a corporate IT department. He recently received some SQL Server security bulletins about attacks that exploit vulnerabilities in various network protocols. Sean wants to update each SQL Server 2000 installation in the network with the necessary patches. As an added security measure, Sean also wants to disable unnecessary protocols on the server. However, in Sean’s company, the standard protocol for communications between SQL Server and clients is TCP/IP. Help Sean write a script that disables all protocols except TCP/IP for his SQL Server installation.

Solution:

Sean can use SQL-DMO to manipulate the network library settings for SQL Server. SQL-DMO exposes various objects that Sean uses to manage a SQL Server configuration, SQL Agent settings, and other tasks. For reading and changing the registry settings for SQL Server, Sean can use SQL-DMO’s Registry object. The Registry object exposes the registry settings of SQL Server configurations, such as startup parameters, the SQL Mail account, and default database paths. The Registry object’s SuperSocketList property returns the list of protocols enabled on the server side. To specify the protocols, Sean uses a short form that looks like:

Named Pipes - np
TCP/IP - tcp
Multiprotocol - rpc
IPX/SPX - spx

Now, Sean can write a VBScript program that can use the SQL-DMO Registry object to change the enabled protocols on the server to meet the security standards. Listing 1 performs the operation on a specified SQL Server. Sean also uses the script to check the protocol settings and enable only the TCP/IP protocol for the specified SQL Servers.

AUGUST READER CHALLENGE:

Now, test your SQL Server savvy in the August Reader Challenge, "A Bulk-Copy Procedure" (below). Submit your solution in an email message to [email protected] by July 15. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Larry is a database analyst who maintains a reporting database for the company's data warehouse. He must periodically synchronize the reporting database with the data warehouse and other external data sources. The reporting database contains a table that stores a list of companies that bulk copy program (bcp) loads from a data file. The data file always contains the complete list of companies. The following sample schema for the Companies table is created from the Northwind database’s Suppliers table:

USE Northwind
GO
SELECT SupplierId AS CompanyId, CompanyName
INTO Companies
FROM Suppliers
ALTER TABLE Companies ADD CONSTRAINT pk_companies_id
PRIMARY KEY CLUSTERED(CompanyId)
GO

Other tables in the reporting database also reference the Companies table. When Larry tries using bcp to load the data file containing the list of companies into the Companies table, he gets a primary key violation because of the unique constraint on the CompanyId column. Larry needs to load only new companies into the table from the data file—he can ignore updates to existing companies. What should Larry do to import the data file with the least amount of coding and redesigning of the schema? The bcp commands must use the same data file for import and insert only the new rows into the Companies table.