Q: Do some NetLibs behave differently when you use Open Database Connectivity (ODBC) connection pooling?
When you use ODBC connection pooling, Named Pipe NetLib connections are slower and might cause erratic behavior. We duplicated anomalous behavior by using ActiveX Data Object (ADO) to connect to SQL Server 6.5 over Named Pipes while we were running Microsoft Transaction Server (MTS). The system responded with the error message Unable to read login packets. As a general strategy when you're running MTS, use the same connection parameters (i.e., username and password) to help provide maximum reuse of connections.
Q:
Does installing SQL Server on a domain controller affect security and performance?
To avoid performance and security problems, we encourage you to install SQL Server on a member server in a domain, rather than a Primary Domain Controller (PDC) or Backup Domain Controller (BDC). Performance is affected because the domain controller consumes memory, CPU, and other resources that a member server would delegate to SQL Server. Performance degradation is not noticeably great, unless you don't have enough domain controllers to handle the network load.
Many people are familiar with these performance problems, but not everyone realizes the potential security risks associated with SQL Server installed on a domain controller. SQL Server is a Windows NT service called MSSQLServer. Like any NT service, MSSQLServer must run within the security context of an NT account. This configuration lets users assign permissions and rights to a service by assigning those permissions and rights to the underlying NT account. You have two account choices: You can run a service as LocalSystem, which is an administrator account with powerful rights and privileges, or you can run a service as normal, which is an account with the rights of an ordinary system user. By default, the setup program installs MSSQLServer to run as a LocalSystem account, giving users powerful rights and privileges.
This default setting can lead to security problems. For example, xp_cmdshell is an extended stored procedure that lets SQL Server users issue a Transact SQL (T-SQL) command as if they were typing in a command prompt window. If a user types the command
xp_cmdshell "dir c:\"
from a T-SQL session, the system returns the output "dir c:\" as the query's result set. This result is benign, but xp_cmdshell executes in the same user security context of the NT account running the MSSQLServer service. Thus, users who have the right to execute xp_cmdshell also have the right to perform
xp_cmdshell "erase c:\*.* -F -S -Q"
This command is a full recursive deletion of the root directory. For even more fun, a SQL Server system administrator (sa) can type
xp_cmdshell "net" **inc
This command lets sas turn their personal NT accounts (i.e., with ordinary user privileges) into a domain administrator account.
You can avoid these security holes (even if you install SQL Server on a domain controller) by running MSSQLServer under an account other than LocalSystem and without domain administrator privileges. However, to contain potential security problems and not degrade performance, your best option is to put SQL Server on a member server rather than a domain controller.
Q:
What is the best way to make my tempdb smaller?
We see tempdb-related questions often in SQL Server newsgroups. Tempdb defaults to 2MB, so you generally want to expand tempdb rather than shrink it. However, here's our recipe for shrinking tempdb:
- Use sp_configure to put tempdb into RAM.
- Stop and restart SQL Server.
- Create a new device to hold your tempdb database, and select it as the default device.
- Cancel the selection of the master device as the default device.
- Use sp_configure to remove tempdb from RAM.
- Stop and restart SQL Server.
Your tempdb database is now on the device you created. It is the same size as the default database size in sp_configure, so you need to shrink or expand tempdb as necessary (some queries will use much more space in tempdb than the default 2MB).
By moving tempdb into RAM, you remove all references to existing disk device usage. Taking tempdb out of RAM creates a new database that is backed by disk storage on the first device in the pool of default disk devices. By default this pool includes only master.
Prev. page
[1]
2
3
4
next page 
