Peer-to-Peer Questions #22: Domain Controller, Using Scripts for Databases, Dual Booting
March 27, 2000
Editors Note This article, culled from the TechNet Web site (https://www.microsoft.com/technet), answers the most interesting questions received on the peer -to-peer discussion groups over the past few weeks. To post your own questions, visit the TechNet discussion groups at https://www.microsoft.com/technet/community/newsgroups/default.mspx.
On This Page
Domain Controller taking a little break
Creating a Database using scripts, and loading initial data from text files
Magical Boot!
Domain Controller taking a little break
Nathan Fain posts this issue in the Windows NT Domain forum:
Q. A lot of my users are getting the warning/error message upon login:
"A Domain controller for your domain couldn't be contacted. You've been logged in using cached info."
And yet, after the user presses OK everything is as usual. There are no apparent problems because of this message. I know that the user is having no trouble contacting the server. There are no special setups or situations on the network, such as segments or such (though we do have a few Linux and Unix machines with SAMBA setup as CLIENTS)
What causes this message? How should I go about solving this problem (where to look)?
A. IT Pro extraordinaire Tom Phelan takes time out of his busy day to provide the following information and feedback to fellow IT Pro Nathan Fain:
First, I'm assuming the users are logging on to Windows NT workstations with domain passwords (rather than local machine passwords).
Your domain controller has a problem, which needs to be looked at! You said that the user isn't having trouble finding the server; you're not right there. The user IS having trouble finding a domain controller to logon to. Just because you can ping a domain controller or even transfer files does not mean it is able to properly authenticate users.
If a Windows NT computer cannot find a domain controller to logon to, the computer will used "cached credentials" and allow the user access anyway even though the user was never authenticated with the domain controller. The idea is that if the user successfully logged on before, Windows NT gives the user the benefit of the doubt and provides access.
This is a security risk that I'm unwilling to allow. For example, suppose I successfully log on to your network today. Tonight you find that I have been loading viruses on the system so you disable my domain user's account. Knowing what I do about cached credentials, I pull the Ethernet cable—so the computer won't find a domain controller—and then log on to the workstation. Guess what, I'm allowed to logon using cached credentials!
Fortunately, there is an easy solution. With a registry entry change you can prevent the use of cached credentials forcing the user's computer to find a domain controller before allowing the user to logon. If the network goes down, a person can log on using a local account rather than a domain account.
Here is the registry setting change.
Go to the following: HKEY_LOCAL_MACHINE \Software \Microsoft \Windows NT\CurrentVersion\Winlogon
Create a DWORD entry called CachedLogonsCount and set its value to zero (this is the default value I think).
I read about this in Minasi's book, Mastering Windows NT Server--6th edition, on page 411 (ISBN 0-7821-2445-3). I highly recommend this book if you manage an NT network.
Thanks Tom! Now, here are a couple of good Knowledge Base articles, one that explains a problem with Network Address Translators causing delays and the second article explains how to increase the timeout period for logging on to the domain:
Q172227: Network Address Translators (NATs) Can Block Netlogon Traffic
Q163204: Increase Domain Logon Timeout over Network
Creating a Database using scripts, and loading initial data from text files
"M" asks the following question. Since no specific information was provided regarding the version number that "M" is using, we'll make the assumption that it is SQL Server 7.0.
Q.* *I would like to know the correct/easier/automatic way of doing the following:
I want to create a new database from the scripts generated from an existing database.
I want to import/load data into some of these tables from text files.
My method is this:
Create a new database using Enterprise Mgr.
Open the script file in SQL Query Analyzer and run it to create tables, triggers, etc.
Open the DTS Wizard from Enterprise Mgr to import data from text files.
Is there a better and efficient way of doing this?
Several IT Pros chipped in to see if they could help make "M's" life easier. This first suggestion comes from IT Pro Paul:
A. In order to automate the process, you will need to script the create database and objects (DB, tables, keys, constraints), then BCP in the data from the text files.
I take it you're using SQL Server 7. I don't know if it will generate CREATE DB scripts, but it should churn out the code for the tables. Then, create a dos batch file that executes your scripts. You can set this file to run whenever you want.
A. Jason Occhialini covers the BCP (Bulk Copy Program) part of M's question:
Don't forget about BCP. It can be quite handy for extracting and inserting data, especially if you want to go command line or create scripts to perform the operation. It's really pretty straightforward to use. Here's a simple example of copying a table out and back in:
bcp TestDB.dbo.Test1 out Test1.txt -c -q -SSERVERNAME -Usa -Pwhatever
bcp TestDB.dbo.Test1 in Test1.txt -c -q -SSERVERNAME -Usa -Pwhatever
These are simple examples. You can make it as simple or complicated as you choose.
A. Next, IT Pro Itzik Ben-Gan provides the following extremely detailed process that addresses scripting the creation of the database objects:
Here's a proc that would make step 1 easier.
CREATE PROC sp_new_help_revdatabase
as
SET NOCOUNT ON
DECLARE @dbname varchar(80),@lname varchar(50) ,@fname varchar(255),
@size VARCHAR(30) ,@filegrowth varchar(10), @filegroup varchar(40), @str
varchar(50),
@status varchar(4), @type VARCHAR(30)
SET @dbname = db_name()
EXECUTE ('DECLARE CSR CURSOR FOR
SELECT RTRIM(name), RTRIM(filename),
case
when size >= 128 then CONVERT(varchar(30), size * 8 / 1024) + ''MB''
else CONVERT(varchar(30), size * 8) + ''KB''
end as SZ ,
''growth'' =
case
when sf.status & convert(int, 0x00100000) = convert(int, 0x00100000) then
growth
when growth >= 128 then (growth / 128)
else growth * 8
end
,groupname,
case
WHEN sf.status & convert(int, 0x00100000) = convert(int, 0x00100000) THEN
''%''
WHEN growth >= 128 THEN ''MB''
ELSE ''KB''
End as Status,
convert(varchar(10),case sf.groupid
when 0 then 99999
else sf.groupid end) as Type
From ' + @dbname + '..sysfiles sf left outer join ' + @dbname +
'..sysfilegroups sg on sf.groupid = sg.groupid
order by type')
set @str = " "
OPEN CSR
PRINT 'CREATE DATABASE '+ @dbname
FETCH Next FROM CSR INTO @lname ,@fname ,@size ,@filegrowth, @filegroup
,@status,@type
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
if @str = @filegroup
begin
PRINT ','
PRINT ' ('
PRINT ' NAME =['+ @lname + '] ,'
PRINT ' FILENAME = "' +@fname + '", '
PRINT ' SIZE = ' + @size + ','
PRINT ' FILEGROWTH = '+@filegrowth+@status
PRINT ' )'
set @str = @filegroup
end
else if @str is null
begin
PRINT ','
PRINT ' ('
PRINT ' NAME =['+ @lname + '] ,'
PRINT ' FILENAME = "' +@fname + '", '
PRINT ' SIZE = ' + @size + ','
PRINT ' FILEGROWTH = '+@filegrowth+@status
PRINT ' )'
set @str = @filegroup
end
else if @type = '1'
begin
PRINT 'ON ' + @filegroup +''
PRINT ' ('
PRINT ' NAME =['+ @lname + '] ,'
PRINT ' FILENAME = "' +@fname + '", '
PRINT ' SIZE = ' + @size + ','
PRINT ' FILEGROWTH = '+@filegrowth+@status
PRINT ' )'
set @str = @filegroup
end
else if @type between '1' and '9999'
begin
PRINT ','
PRINT 'FILEGROUP ['+@filegroup+']'
PRINT ' ('
PRINT ' NAME =['+ @lname + '] ,'
PRINT ' FILENAME = "' +@fname + '", '
PRINT ' SIZE = ' + @size + ','
PRINT ' FILEGROWTH = '+@filegrowth+@status
PRINT ' )'
set @str = @filegroup
end
else
Begin
Print 'Log ON'
PRINT ' ('
PRINT ' NAME =['+ @lname + '] ,'
PRINT ' FILENAME = "' +@fname + '", '
PRINT ' SIZE = ' + @size + ','
PRINT ' FILEGROWTH = '+@filegrowth+@status
PRINT ' )'
set @str = @filegroup
end
END
FETCH Next FROM CSR INTO @lname ,@fname ,@size ,@filegrowth, @filegroup,
@status,@type
End
Deallocate csr
Next, Dan Guzman provides this follow-up that will take a script like the one above and make M's life even easier by running the above scripts from a command prompt:
Assuming you want to repeat a database setup from the same scripts, then create the Transact-SQL 'create database script' and then create a BAT file to run OSQL and then BCP.
OSQL /SServerName /E /dmaster /iCreateDatabase.sql
OSQL /SServerName /E /dDatabaseName /iCreateDatabaseObjectScripts.sql
BCP DatabaseName.dbo.TableName in TableData.txt /SServerName /T /c
If you have a lot of data to import, you might consider using fast BCP and creating the indexes after the import.
Thanks everyone for your very good ideas and suggestions. I thought that some clarification would be helpful. The utility "OSQL" and "ISQL" are command-line utilities that are used to enter Transact-SQL statements, system stored procedures, and script files from the command prompt. The difference between the two is that ISQL uses DB-Library to communicate with SQL Server and OSQL uses ODBC (Open DataBase Connectivity).
So, how are these create table scripts generated, in the first place? In SQL Server 7.0, this is done in Enterprise Manager. Here are the steps.
How to generate a script in Enterprise Manager
To generate a script
Expand a server group; then expand a server.
Expand Databases, right-click the database to script, point to All Tasks, and then click Generate SQL Scripts....
On the General tab, select the database objects to script. By default, all objects in the database are scripted.
On the Formatting tab, select from the script formatting options:
Generate the CREATE <object> command for each object so that each object to be scripted is explicitly created using its existing definition. This is selected by default.
Generate the DROP <object> command for each object so that a DROP statement is added to the script for each object to be scripted. This is selected by default.
Caution: When executed, this causes any existing objects in the database where the script is executed with the same name as objects listed in the script to be deleted first.
Generate scripts for all dependent objects so that all objects in the database, which are needed to create the objects listed in the script, are included automatically in the script if not already selected.
Include descriptive headers in the script files so that a comment is added to the file for each object listed in the script.
On the Options tab, select the security-related, table-related, and script file-related options.
On the General tab, click Preview... to view a preview of the generated script.
The Data Transformation Service wizard is a great utility included with SQL Server 7.0 that allows DBAs to move data in to a SQL Server database.
In SQL Server Books OnLine, there is a good section addressing BCP performance that would help those less familiar with BCP. Run the following query in Books OnLine:
Optimizing BCP
Magical Boot!
Here's a perplexing issue regarding dual booting:
Q. I have installed boot magic to dual boot Windows 98 on drive C: and Linux Mandrake on drive D: . Problem: Cannot get to the boot menu now when I hold down ctrl or f8 button.
Any suggestions?
A. Microsoft MVP Doug Knox jumps right in with his suggestion:
For Windows 98, open Windows Explorer, and find MSDOS.SYS (hidden/read-only file)
Add the following lines to the end of the file, or make them match if they're all there already
BootMenu=1
BootMenuDelay=15
BootMenuDefault=1
BootKeys=1
You can adjust the BootMenu delay to a lower value if desired.
Oh, and Doug wants us all to know that the Microsoft MVP Program does not constitute employment or contractual obligation with Microsoft Corporation. We do this for fun!
We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.