Skip navigation

Questions, Answers, and Tips About SQL Server - 01 Jun 1998

Q: I have SQL Server 6.5 and the SQL Enterprise Manager (EM) installed on my computer. When I was digging in my Registry, I found that the username and password I'd used to register the three SQL Server machines running on our network appeared unencrypted, in plain text, at HKEY_CURRENT_USER\Software\Microsoft\MSSqlServer\SQLEW\Reg-istered Servers\SQL6.5\server name, where server name was the name or IP address I used to register the SQL Server. Should I be worried that the server name isn't safe?

EM stores the clear text version of the username and associated password you used to register the server in the Registry on the client machine. EM stores the information as binary data, so it might not look like clear text. To see the password information, select the Registry key you mentioned, go to the View menu, and select the Display Binary Data option. You'll see a display similar to Screen 1, page 220. As you can see, the local server NITTANY is registered with an sa login and the password secret.

Unfortunately, you can't turn off this behavior; EM will store passwords unless you used a trusted connection when you registered the server. The good news is that relief is just a version away--Microsoft has fixed this problem in SQL Server 7.0 beta 2.

Q: How is Microsoft tightening security in SQL Server 7.0?

SQL Server 7.0, like SQL Server 6.5, can operate in two security (authentication) modes: Windows NT authentication mode (NT authentication only) and mixed mode (NT authentication and SQL Server authentication). Mixed mode lets users use either NT authentication or SQL Server authentication to connect. Users who connect through an NT user account can use trusted connections in NT authentication mode or mixed mode. After a user has successfully connected to SQL Server, the security mechanism for both modes is the same.

In SQL Server 6.5, security systems based solely on SQL Server logins and passwords are easier to manage than security systems based on NT user and group accounts, especially for nonmission-critical databases and applications without sensitive or confidential information. For example, you can create one SQL Server login and password for all users of an application, rather than creating all the necessary NT user and group accounts. A shared login and password for all users, however, removes the ability to track and control the activities of individual users.

Under the SQL Server authentication model, when a user connects with a specified login name and password from a nontrusted connection, SQL Server checks whether a SQL Server login account has been set up and the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails, and the user receives an error.

Microsoft is including SQL Server authentication in SQL Server 7.0 for backward compatibility for two reasons. First, applications written for SQL Server versions earlier than SQL Server 7.0 might require SQL Server logins and passwords. Second, connections with non-NT and Internet clients require SQL Server authentication.

SQL Server 7.0 introduces the much-needed notion of role-based administration. SQL Server 7.0 parcels out the tasks, chores, and privileges that the sa login handled into several fixed server roles: Sysadmin (which spans all other server fixed roles), Serveradmin, Setupadmin, Securityadmin, Processadmin, Dbcreator, and Diskadmin. Similarly, SQL Server 7.0 divides the Database Owner (DBO) chores into nine fixed roles, including db_accessadmin and db_securityadmin. Among other enhancements are an explicit DENY command to complement GRANT and REVOKE, and NT authentication support for all NetLibs. For individuals using the pure NT authentication mode, the new version eliminates double-membership administration headaches.

Q: Does SQL Server 6.5 have a setting to display the seconds of a datetime field on a SELECT statement? When I perform a bulk copy program (bcp) or an INSERT, I'm careful to include the seconds, but I want to display seconds at query time.

The solution is to use T-SQL's CONVERT function. CONVERT lets you explicitly convert one datatype to another (e.g., from char to int), and lets you specify a display mask for a datetime column. Table 1 shows the list of available datetime display masks from SQL Server Books Online (BOL). Listing 1 is a simple SELECT FROM pubs..sales, and Table 2 shows sample output using different display masks. To obtain a datetime display in style 13 output, such as dd mon yyyy hh:mm:ss:mmm, which Table 3 illustrates, use the CONVERT function shown in Listing 2.

Q: Microsoft is gearing up for using SQL Server for data warehousing. Can you recommend any resources to help me get up to speed?

Many people consider Ralph Kimball's The Data Warehouse Toolkit (John Wiley & Sons) a classic, and we also like The Official Client/Server Guide to Data Warehousing by Harjinder S. Gill, Prakash C. Rao, and Harjinder Gill (Que). Table 4, page 222, lists Web sites that offer information about data warehousing, repositories, and metadata.

Q: What suggestions do you have for using a RAID subsystem to increase the speed of I/O-bound database applications?

A good hardware-based RAID subsystem will help high-end I/O throughput for I/O-bound applications, but remember to bump up the de- fault cache size for the controller. Significantly increasing the controller's cache (e.g., from 8MB to 32MB) usually costs just a few hundred dollars, but this increase can have a huge effect on I/O-bound (as opposed to calculation-intensive) database applications.

Q: I've heard that if you use raw partitions (as UNIX programmers do), you can get better perfor- mance by eliminating the middleman (i.e., Windows NT). How do you use raw partitions for data devices in SQL Server?

Raw devices are UNIX-style unformatted raw disk partitions. Many people with strong UNIX backgrounds are tempted to use raw partitions because this approach is the preferred technique under most UNIX systems. Although NT supports raw partitions as a way to avoid the file systems (FAT or NTFS) layer for a database server, using raw devices is unusual and not recommended in NT. A Microsoft source explained that raw devices are a legacy from days when file systems were naive about parallelism and the enhanced performance that symmetric multiprocessing (SMP) can offer. Raw devices no longer offer a significant performance advantage, and they have significant administrative disadvantages. However, under SQL Server 6.5, you can create a device on a raw partition using the DISK INIT statement with this general syntax:

DISK INIT

NAME = 'logical_name',

PHYSNAME = 'physical_name',

VDEVNO = virtual_device_number,

SIZE = number_of_2K_block

\[, VSTART = virtual_address\]

where NAME refers to the logical name of the database device and PHYSNAME is the drive letter, path, and filename of the database device.

To create a device on a raw parti-tion, use only the partition drive letter for the PHYSNAME parameter in the DISK INIT statement. The following example creates a 200MB device named Inventory_Dev on the raw partition corresponding to the G drive:

DISK INIT

NAME = 'Inventory_Dev',

PHYSNAME = 'G:',

VDEVNO = 16,

SIZE = 102400

The SQL Server 6.5 documentation mentions several limitations to consider when you use raw partitions:

1.You can create only one device on each raw partition. You must configure the logical partition as one device, because the raw partition has no file system.

2.You can't perform ordinary file system operations such as copy, move, and delete on raw partitions, nor can you take advantage of file service operations such as bad block replacement.

3.You can't use the NT Backup utility to back up devices located on raw partitions. However, you can back up databases and transaction logs stored on raw partitions with the Transact-SQL (T-SQL) DUMP statement or through the SQL Server Enterprise Manager (EM).

SQL Server 6.5 supports raw devices, but SQL Server 7.0 won't. SQL Server 7.0 allocates device sizes dynamically--you don't have to preallocate device sizes.

Q: Can you use a Remote Access Service (RAS) connection to replicate between SQL Servers?

Yes, RAS lets you perform replication over a dial-up connection. But to use this method effectively, you want to keep the amount of replicated data to a minimum. To make RAS work, you need to ensure that the central and remote server are in the same domain or that the remote server is in a domain that trusts the domain the central server is in. Then, you need to install RAS on the remote server (enabling it to accept dial-in calls) and set up the central server with permission to dial out.

When you make the initial RAS connection from the central server to the remote server, you want the RAS dialer to save the password so that you can make subsequent RAS connections without user intervention. (You must use Named Pipes or the Multiprotocol NetLib to run replication.) Then, set up the actual replication, changing the distribution task timing to One Time execution or Scheduled execution, as you require. Manually experiment until you determine the sequence of events, how long they seem to take, and the timing you want. You might want to use a sequence of stored procedures to accomplish the following tasks:

*Determine the status of the distribution flag on the remote server

*Return date and time from the remote server and synchronize times

*Consider using a table on the central server to store RAS connection history

*Initiate a RAS replication task according to a schedule

*Write error-handling routines

Q: We are looking for a utility to perform database comparisons accurately and give us a high level of confidence in the lists of differences. In your November column, you mentioned the BackOffice Resource Kit's SQL Server Object Comparison utility. However, we haven't had good experiences with this tool. What third-party utilities can you suggest?

Desktop DBA and Enterprise DBA from Platinum Technology (http://www .platinum.com) provide commercial-grade database comparisons at the database-object level, but not at the data level. However, the next versions of these utilities (due midyear) can do full database comparisons. DBArtisan, from Embarcadero (http://www.embarcadero .com), currently doesn't offer this function, but an Embarcadero representative told us that the company is developing another product (slated for July release) that is a full database compare-and- synchronize utility. The utility will apparently compare databases on any number of servers, display differences (and similarities), and let the user synchronize selected schema changes or global changes. We suspect other util- ity vendors, including BMC Software (http://www.bmc.com) and Sylvain Faust (http://www.sfi-software.com), will also offer tools with this capability in future product releases. SQL Server Professional (http://www.pinpub.com/sqlpro/home.htm), a monthly newsletter from Pinnacle Publishing, has published two relevant articles with code in the October 1996 and January 1998 issues.

Q: What is the best way to import the data from a FoxPro database to SQL Server 6.5?

We can think of two approaches. You can export your data into Microsoft Access and then use the appropriate free upsizing wizard from Microsoft. Or you can export FoxPro data as text files, and then use bulk copy program (bcp) to import them into SQL Server. You create a format mask--a tedious process, but possible. For details about format masks, read our August 1997 column or refer to SQL Server Books Online (BOL).

Q: I want to install SQL Server on a very fast hard disk, but the setup program stops responding and I get an error message. Do you know a workaround?

This problem demonstrates that faster isn't always better. Refer to the Microsoft Knowledge Base article "BUG: SQL Server Setup May Stop Responding on a Very Fast Drive" (http://support .microsoft.com/support/kb/articles/q180/6/49.asp). The article explains that when you install SQL Server on a very fast hard disk, such as an EMC Symmetrix, the setup program sometimes stops responding on the Setup is now installing the initial SQL Server configuration screen while executing the Instmsdb.sql script. SQL Server will create the Instmsb.out file, but the file will be empty, and the error log will contain the following messages:

98/01/28 12:28:31.43 spid11 Database 'msdb' cannot be opened - it is currently being created. Wait and try query again.

98/01/28 12:28:31.43 spid11 Unable to proceed with the recovery of dbid <5> because of previous errors. Continuing with the next database.

An access violation (AV) might follow these errors.

The workaround is to copy the SQL Server installation files to the local hard disk to edit the Instmsdb.sql file. Add a WAITFOR DELAY '0:00:01' statement to the beginning of the Instmsdb.sql script.

Q: Where can I obtain information about Open Database Connectivity (ODBC) error messages?

When you get errors while using Data Access Object (DAO) or Remote Data Objects (RDO) to access ODBC databases, Visual Basic (VB) provides a generic ODBC Call Failed error message with no specific detail, and you have to cycle through the Errors collection to get additional information. Microsoft Knowledge Base article "HOWTO: Get More Information on the ODBC Call Failed Error" (http://support.microsoft.com/support/kb/ articles/q161/2/88.asp) does a good job of explaining how to get more information about this error. The article provides sample code for setting up an error handler to help you retrieve more useful information. Another classic article is "How To Retrieve Information from RAISERROR Function in SQL Server DB" (http://support.microsoft.com/ support/kb/articles/q120/7/63.asp).

TAGS: Security SQL
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish