BCP Beginner - Please Help

Hi,

I am completely new to the BCP utility and fairly new to SQL Server

I am learning from a book and I am trying the following example (the server
I'm learning on is called contractor and a password has not been give to the
sa)

bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor

When I run this in Query Analyser i get the error message..

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

I have tried puuting quotes around the database and the table name as
follows-

bcp "pubs..authors" out authors.txt -C -r \n -t, -U sa -P -S contractor

and get the error

Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.

Please can anybody help me to get this first bit working?

Thanks in anticipation.In your bcp statement, is the -C supposed to indicate that it is a
character file? If so, it needs to be lowercase (-c), otherwise it
indicates the code page, which expects a value. Also, I don't know if
this makes a difference, but I've never used spaces between the options
and the values (for example, -Usa instead of -U sa). I thought I
remembered both working, but in the help I didn't see anything about
the space being allowed and the samples given do not have spaces.

HTH,
-Tom.|||Stevie D (Steve@.127.0.0.1) writes:
> I am completely new to the BCP utility and fairly new to SQL Server
> I am learning from a book and I am trying the following example (the
> server I'm learning on is called contractor and a password has not been
> give to the sa)
> bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor
> When I run this in Query Analyser i get the error message..

You don't BCP from Query Analyzer. You run it from a command-line
window.

And, as Thomas pointed out, you should use -c and not -C. The
spacing should be OK, though.

> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.

(The reason you get this syntax error is that bcp is not an SQL
command. Everything that is an identifier, but not a command is
taken as a stored procedure command. Next token is pubs. When calling
stored procedures quotes around strings that follow identifier syntax
are optional. (This is to permit us to write "sp_help tbl", instead
of "EXEC sp_help 'tbl'".) When the dot comes there is no longer
any saver.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Basic questions on Service Master Key

Hi. I'm reading about encryption and not totally up-to-speed on what the Service Master Key is all about...

Let me paraphrase this book: Microsoft SQL Server 2005 Implementation and Maintenance Training Kit by Microsoft Press, while I ask questions...
__

The database engine uses the Service Master Key to encrypt...
- Linked server passwords (example?)
- Connection Strings (example?)
- Account credentials (example?)
- All database master keys

You should back up the Service Master Key and store it offsite.
(Even if I never plan to use encryption?)

ALTER SERVICE MASTER KEY REGENERATE regenerates the service master key.
(Is it building a completely different key, or regenerating the original?)
(Why would I want to regenerate it?)
(Does this void all database master keys encrypted by the original Service Master Key?)
(If so, do I regenerate all database master keys voided by the regeneration?)

A database master key is optional.
(So if I have Server A with encrypted database A, that I want to restore to Server B,
I need the following items to perform restore?
- a backup of database A
- the Service Master Key of A at time of backup
- associated password of Service Master Key at time of backup)

Just repeating what it is mentioned in BOL:

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Regenerating or restoring the Service Master Key involves decrypting and re-encrypting the complete encryption hierarchy. Unless the key has been compromised, this resource-intensive operation should be scheduled during a period of low demand.

Also http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx fyi for more information.

|||

A few corrections:

The Service Master Key (SMK) has two encryptions, both made using DPAPI - one uses the service account credentials, the other one uses the machine credentials. Note that neither of these encryptions is necessarily protecting the SMK from a machine administrator.

Regenerating the SMK will only require re-encrypting the entities encrypted by it: linked server login passwords (the ones you specify when you set up a linked server), credentials (as created by CREATE CREDENTIAL), and database master keys.

Coming back to the original questions:

It is not necessary to backup the SMK if you do not plan to use it.

REGENERATE creates a new key. It can be used as part of a key rotation policy or if you have reason to believe the old key was compromised. You can also use this to generate a new key if the old one was corrupted and you have no backup around. Regenerating the SMK doesn't invalidate any database master key (DbMK) - it just re-encrypts them with the new key.

For restoring a database to another server, see last paragraphs of http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx. You don't need to move the SMK to the new server, you just need to open and reencrypt the DbMK with the new server's SMK, and you only need to do this if you had the same setup on the original server.

Thanks
Laurentiu

|||

Thanks for taking the time to explain some concepts. I haven't had any experience with encryption, and now feel further behind with SQL 2005's new concepts.

I'm having problems understanding the extent the SMK is capable of.... you wrote about the SMK being compromised, but I don't see how you could compromise it, or what you could do with it once you have it?

- You have to be a SysAdmin to restore a SMK
- You would somehow have to get the SMK backup file onto the server
- You would have to have local access to the SMK backup file to restore it
- You would have to know the password of the restored SMK

And even if all that occurs:
- The linked servers all still show "********" for a password
- You can't decrypt data with a SMK (can you?)
- You can't decrypt a Database Master Key with the SMK (can you?)

|||

The SMK allows you to decrypt all that is encrypted beginning with it - database master keys, initially, then anything encrypted by the database master keys, and so on, towards the data itself.

As you've noticed, there are good protections set up around accessing the SMK, so my mention of "compromising" is more theoretical than practical - you could "loose" the SMK by loosing an SMK backup and the password protecting it, for example.

Having the SMK and a copy of a database with encrypted data (where the encryption protection is rooted at the SMK) allows you to decrypt the data.

Have a look at the encryption hierarchy in Books Online, to understand what key protects what information. The main point is that this is a hierarchy, so the key at its root unlocks everything below. You can detach protection of some data from this hierarchy by using password encryption for certain keys, which uses TripleDES encryption to protect those keys.

http://msdn2.microsoft.com/en-us/library/ms189586.aspx

Also listen to the presentation of encryption in SQL Server 2005 that Rob Walters gave at PASS this year. You can access Rob's presentation here:

http://cmcgc.com/Media/WMP/261115/

Also, there were no encryption features in SQL Server 2000. These features have been introduced in SQL Server 2005.

Thanks
Laurentiu