Hard Drive Security, Comparing Two Versions of a DB, and More SQL

Article
10/11/2019

Web Q&A

Edited by Nancy Michell

Q I need to know how to make my laptop as secure as possible because I travel to clients frequently. I'm running Windows® XP.

A Check out the Microsoft Windows XP Security Guide Overview and the Threats and Countermeasures Guide. You should also consider physical security and think about using some of the following:

BIOS passwords
File system encryption with the Encrypting File System (EFS) in Windows XP or third-party software
Data backup
Physical protection—for example, watch your computer while going through security checkpoints, and minimize time spent in public areas

A Check out the Microsoft Windows XP Security Guide Overview and the Threats and Countermeasures Guide. You should also consider physical security and think about using some of the following:

BIOS passwords
File system encryption with the Encrypting File System (EFS) in Windows XP or third-party software
Data backup
Physical protection—for example, watch your computer while going through security checkpoints, and minimize time spent in public areas

Information on the physical protection of laptops is also available. For example, third-party software tracing packages such as the one at ztrace.com trace the location of stolen laptops.

If you use BIOS passwords, remember that systems often include override passwords on a vendor-by-vendor basis. Lists of these passwords are in wide circulation and are easy to bypass by documented procedures (remove the battery and short the contacts, and so on). You should definitely weigh the pros and cons of using these passwords. The BIOS password can, however, buy you time to change passwords, cancel credit cards, disable accounts, assess damage, and so forth, and might prevent a hacker from gaining access to personal information. On many systems, resetting the BIOS can break or trigger tamper-resistant measures, which may be useful as an incident indicator. BIOS passwords do have a role, but they are not going to stop the knowledgeable hacker.

File encryption also has its advantages and disadvantages. It takes all of a minute or so to gain access to NTFS files if the actual drive or laptop has been stolen. NTFS security was designed to protect files from within the OS and via the network, not to protect files when someone has the hard drive and is intent on retrieving the data. EFS or other similar technologies are designed to thwart the data thief when the disk is in hand, but make sure to understand all the implications of EFS. Occasionally someone will turn EFS on without backing up and then they find they need to reinstall the OS. In that case their files become completely inaccessible. The irony is that people tend to turn EFS on for files they care the most about, and then lose them.

Use the related Threats and Countermeasures Guide in the Windows XP Security Guide to evaluate any threats to security, and use the Security Guide to learn about the tools and mechanisms for implementing the protection mechanisms. To prevent unauthorized access to your machine from your various networks, Windows XP includes Internet Connection Firewall (IFC) software you can use to restrict the kinds of information that's communicated between your machine and the network. It's a very important security tool, especially if you're frequently connecting to unfamiliar networks (both wired and wireless). See Use the Internet Connection Firewall for more information. Remember, use software and hardware firewalls when possible.

Q How do I design a script to create an automated comparison of two databases, mainly to detect possible changes in structure, keys, indexes, table names, and so on? Do you have any advice on how to achieve this with VBScript or some other means?

A The in-depth article Compare SQL Server Databases with sp_CompareDB describes the process of creating a stored procedure, which will perform a comparison on two databases including the constraints, indexes, sprocs, and so on.

If you want to make sure the data is identical for all rows and columns, just execute sp_table_validation for the table on each server and compare the checksums. This is used by replication to make sure tables are in sync between publisher and subscriber:

EXEC sp_table_validation @Table = 'Authors', @rowcount_only = 2, -- rowcount and checksum @full_or_fast = 2 -- fast count

The result is the expected rowcount value of 23 and expected checksum value of -1159171265 for the Authors table.

For fun, try the following and then rerun sp_table_validation to compare the checksum to see that they are now different:

update authors set au_fname = 'John' where au_fname = 'Johnson'

If you are only interested in comparing the schema between two databases, you can script both out and use a file difference tool (for example, WinDiff) to compare them. Sp_diffdb, an example procedure in the book, The Guru's Guide to SQL Server Stored Procedures, XML, and HTML (Addison-Wesley, 2001), takes this approach and uses the advanced file difference checker from Visual SourceSafe® and the sp_OA extended procedures in order to automate the whole process.

There are several schema-level comparison tools available in third-party software including Red Gate. Red Gate also has a data-level comparison tool to use on two SQL servers. You can also try DBGhost.

Q When I use DataSets or strongly typed DataSets with expression columns in ADO.NET, I can't update my table without getting a ReadOnlyException. I removed the expression column before the update and then added it again as a workaround. Is there a better way to do this?

A You can create a user-defined UpdateCommand, which updates only the columns that should be updated. The Expression column is, of course, not included in the update command.

Another workaround is to simply catch the RowUpdated event of the data adapter and check whether an error has occurred. If the answer is yes and the error is a ReadOnlyException, then ignore it. Unfortunately, exception raising and handling incur a high performance cost. Don't base your solution on the idea that for each row update an exception is raised and caught. Removing a column before update might not be elegant, but it won't hurt your application's performance as much as catching exceptions.

You can use the DataAdapter's ContinueUpdateOnError property and go through the DataSet's rows after the update to check the rows' errors. The documentation says that ContinueUpdateOnError gets or sets a value that specifies whether to generate an exception when an error is encountered during a row update.

The property value is true to continue the update without generating an exception; otherwise it's false. The default is false. If ContinueUpdateOnError is set to true, no exception is thrown when an error occurs during the update of a row. The update of the row is skipped and the error information is placed in the RowError property of the row in error. The DataAdapter continues to update subsequent rows. If ContinueUpdateOnError is set to false, an exception is thrown when an error occurs during a row update.

Q I need to programmatically create a Jet 4.0 database and define some tables. It looks like I have to use ADOX to create the database, but then I can use ADO.NET to issue SQL Data Definition Language (DDL) for the table creation. Is this correct or can I actually issue CREATE DATABASE SQL DDL to Jet and forego ADOX?

A You do need to use something other than plain ADO.NET, such as ADOX, an extension to the ADO object model (see ADOX API Reference) to create a new Jet database, unless you do something like a SELECT INTO to create a new database from an existing one.

The Knowledge Base article 317867, "Create a Microsoft Access Database Using ADOX and Visual Basic", states that Jet databases can't be created using only ADO, and demonstrates the required ADOX code.

Q Is there a way to specify in the connection string the port number that an instance of SQL Server is listening on? The server I need to reach is not listening on port 1433. The documentation doesn't mention anything about this.

A This is a common question. The answer is to use "server=tcp:servername,portnumber" in your connection string.

Q When does SqlDataReader actually pull bytes over the network?

Here's my scenario:

Run a stored procedure that returns a resultset with one image column and several rows of data, all with valid image data behind them (not NULL)
Move to the first row of data using SqlDataReader.Read
Move to the second row of data using SqlDataReader.Read
Call SqlDataReader.GetBytes on the image column

Were any of the bytes in the image column in the first row of data pulled over the network in Step 2? Where can I find more information about the underlying design of how image data is read using SqlDataReader?

A SqlClient does not communicate directly with the physical network. It's up to the network service APIs to pull the data from the network and buffer it before SqlClient consumes any of it. From SqlClient's view, you simply obtain a data packet from the network service as you need it. On the second read (Step 3) you need to move from the current field to the start of the next row. This might include reading more data packets from the network service API.

To ensure that the image column bytes are not read unless you need that information, you should change your design to query for the image column only when you need the data. Otherwise, leave the column out of your query. In other words, assume that the data is going to be pulled into the underlying layers when you query for it. That is essentially how the default cursors work for SQL Server™. All data is returned as you navigate through the records. It sounds like you are trying to avoid this.

Q I want to convert a positional flat file to a DataSet (preferably strongly typed). I'm thinking of first converting the flat file to an XML document, then loading the XML document into a DataSet with a schema that matches the resulting XML document.

To generate the flat file, I need to describe the fields (names, positions, and so forth) of that file in XML. That would give me two metadata files: one that is used when converting the flat file to XML, and one XSD file that describes the DataSet.

I want to add custom attributes to the XSD (startPos and endPos for each field) so that I can use the XSD for two purposes. The XSD could look like Figure 1. Thus, I only need to maintain one file. I manually parse the XSD file and use it in the process of converting the flat file to XML, and use the XSD (as normally) to generate the DataSet. How can I add these custom attributes?

Figure 1 The XSD

<?xml version="1.0" encoding="utf-8" ?> <xs:schema id="Dataset1" targetNamespace="https://tempuri.org/Dataset1.xsd" elementFormDefault="qualified" attributeFormDefault="qualified" xmlns="https://tempuri.org/Dataset1.xsd" xmlns:mstns="https://tempuri.org/Dataset1.xsd" xmlns:xs="https://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas- microsoft-com:xml-msdata"> <xs:element name="Dataset1" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="SomeData"> <xs:complexType> <xs:sequence> <xs:element name="Name" type="xs:string" minOccurs="0" startPos="0" endPos="10"/> <xs:element name="Address" type="xs:string" minOccurs="0" startPos="10" endPos="22"/> <xs:element name="PostalCode" type="xs:int" minOccurs="0" startPos="22" endPos="26"/> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>

A If you want to infer a schema as a starting point, you can import or link the flat file into a Jet database, then use the Visual Studio® .NET tools to generate the schema and the typed DataSet class file.

Although the Jet OLE DB Provider can read text files with the help of the Text ISAM driver, the Visual Studio .NET tools don't work when you use the Jet OLE DB Provider with anything other than .mdb files. (See Knowledge Base articles 316831, "PRB: Cannot Configure Data Connection to Excel Files in Visual Studio .NET,", and 811241, "Info: Visual Studio .NET Designer Tool Supports Specific OLE DB Providers,".) You can't create a data connection visually or use the DataAdapter configuration wizard to work with any of the file types that Jet only supports through the ISAM drivers.

Got a question? Send questions and comments to webqa@microsoft.com.

Thanks to the following Microsoft developers for their technical expertise: Pete Baxter, Jeffrey Brendecke, Kurt Dillard, Roberto Di Pietro, Bart Duncan, Pablo Fermandois, Fredrik Gunne, Ken Henderson, Michael Howard, Salome Jacob, Jesper Johansson, Wes Johns, Oren Kendel, Douglas Laudenschlager, Adam Lydick, Oliver Pillwein, Angel Saenz-Badillos, Ben Smith, Stephane St-Michel, Klaus Sobel, Chip Switzer, David Switzer, Mario Szpuszta, Paul West, Frank Wiemer, Lothar Zeitler

Additional resources