build sql script

Wednesday, March 7, 2012

Bulk insert and collations

1). I was trying to do bulk insert t temp tables, so because temdb has a
Latin1_General_CI_AS collation, an my database has a
SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
special tables in my database to truncate before bulk inserts.
2). Another question: all fields in bulk insert file created not by me are
nullable varchars,but it is considers an error when there is no fields
terminators in the last row (it is a timestamp) and there is only only row
terminator .
Do I need to fix datafile or I can change something in bulk insert statement
format or settings?
On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:

>1). I was trying to do bulk insert t temp tables, so because temdb has a
>Latin1_General_CI_AS collation, an my database has a
>SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
>special tables in my database to truncate before bulk inserts.
Sorry, I don't see what the question is. You can specify the
collation for any character column in a table, so there is no reason
to use tempdb just to establish a specific colation.

>2). Another question: all fields in bulk insert file created not by me are
>nullable varchars,but it is considers an error when there is no fields
>terminators in the last row (it is a timestamp) and there is only only row
>terminator .
>Do I need to fix datafile or I can change something in bulk insert statement
>format or settings?
I think you are saying that the last row of the table isn't a valid
data row. In the past I have had a quick-and-dirty command line
program written to fix the data file before loading it.
Roy Harvey
Beacon Falls, CT
|||I don't using tempdb to force the particular collation, but to avoid creating
too much bulk crap in my own database. I saw in some of collation relared
threads here the use of collation in the comparisons, but the 'collate'
keyword in create statements didn't help.
Does your answer means that I am right now doing only possible stuff to
resolve my problems?
"Roy Harvey" wrote:

> On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
> <UncleSam89@.discussions.microsoft.com> wrote:
>
> Sorry, I don't see what the question is. You can specify the
> collation for any character column in a table, so there is no reason
> to use tempdb just to establish a specific colation.
>
> I think you are saying that the last row of the table isn't a valid
> data row. In the past I have had a quick-and-dirty command line
> program written to fix the data file before loading it.
> Roy Harvey
> Beacon Falls, CT
>
|||On Fri, 27 Jul 2007 06:02:01 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:

>I don't using tempdb to force the particular collation, but to avoid creating
>too much bulk crap in my own database. I saw in some of collation relared
>threads here the use of collation in the comparisons, but the 'collate'
>keyword in create statements didn't help.
>Does your answer means that I am right now doing only possible stuff to
>resolve my problems?
When I need a few staging tables for imported data I generally put
them in the target database with an _Imported suffix on the name. When
I have a LOT of tables for importing data, or the import process
becomes too large or complex, I create a Staging database just for
those tables.
It might be worth mentioning that I never import data directly into
production tables, always into staging tables of some sort. In some
cases I even build history tables for the bulk loaded data and assign
batch identifiers so I can trace back any problems to their origin -
particularly useful when the data source is third party with a track
record of screwing things up.
I never explicitly create any objects on tempdb in production as
tempdb is reinitialized each time SQL Server starts and anything in
tempdb is lost.
Roy Harvey
Beacon Falls, CT

BULK INSERT and BCP Active Directory Delegation after SQL 2005 migration

Not sure if anyone else out there is having this problem but we recently migrated some of our databases to SQL 2005. We have some processes that use BULK INSERT or BCP to import the data. On the new server I required that the files we import are stored and held on the FileStore not the database server.

If the user uses SQL authentication this work fine. However, if the user is using Integrated Authentication it fails saying that it could not find the file or access is denied.

The SQL Server services are using a domain account and they have access to the share where the file resides, otherwise SQL authentication would not work.

After some web searches I found out that in SQL 2000 would automatically impersonate as the SQL Service account and they figured that was a security hole (which it is) so now in 2005 you can't do that without allowing kerberos delegation which my Active Directory guys forbid because it is "evil".

So the only other thing I can think of is to use certificates but how do I go about that? Or is there anything else I can do besides use SQL logins.

Just a side note since I'm talking about BCP what happened in version 9.0? We had to grab version 8.0 in order for the bloody thing to work.

Thanks

Rich

Ok, worked with HP and figured out how to fix it. In case any of you are interested below is the documentation I created for myself:

Process Details and Instructions

1. The issue here is using Integrated Authentication to execute a BULK INSERT command (or BCP) from a client workstation, which will retrieve a file that is located in a different location other than the target SQL Server. This will result in a "double hop" authentication that will not work unless you have certificates or delegation.

A couple of things to make sure that are done before reaching this stage.

ü The SQL Service is running under an Active Directory account

ü The SQL Service account has sufficient rights on the SQL Server (Local Admin)

ü The SQL Service account has rights to the remote file via network share

ü The user executing the command has rights to the remote file via network share.

SQL 2000 - This should not be an issue with SQL 2000 because it will automatically delegate on your behalf that is a considered a security hole.

SQL 2005 - They fixed up the security hole by not allowing delegation unless allowed in Active Directory.

You can reproduce the error by executing something like this against the SQL 2005 server:

CREATE TABLE #In_File(In_Data varchar(2000))

BULK INSERT #In_File FROM '\\share\SQL\DEV\20061106.csv' WITH (FIELDTERMINATOR='\0', ROWTERMINATOR='')

The results should look something like this:

Msg 4861, Level 16, State 1, Line 6

Cannot bulk load because the file "\\share\SQL\DEV\20061106.csv" could not be opened. Operating system error code 5(Access is denied.).

If you were to execute the statement on the SQL Server itself then it will work. If you were to move the file locally to the SQL Server this will work. Both will work because you are no longer doing a double hop to execute and get the file.

2. Now that we have the error we are going to check the Service Principal Name in Active Directory to ensure that the service can be delegated to do this task. To do this we need the utility setspn.exe which is available in the Windows Resource Kit or can be downloaded here (http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en> )

After installation on your workstation run the following from command prompt:

C:\Program Files\Support Tools> setspn -L serviceaccount

(eg C:\Program Files\Support Tools> setspn -L SQLAccount)

One result you will get is this:

Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=Domain,DC=corp:

The result you should get is this:

Registered ServicePrincipalNames for CN=SQLAccount,OU=Users,OU=Vancouver,DC=domain,DC=corp:

MSSQLSvc/sqlserver.domain.corp:1433

Notice the extra line MSSQLSvc/sqlserver.domain.com:1433? This "binds" the account to the SQL Service to allow the Service to delegate as the account.

3. Now that we verified that there is no SPN for the MSSQLSvc let's create one. All we do is use SETSPN to add the service by executing the following:

Setspn -A MSSQLSvc/sqlserver.domain.corp:1433 Domain\SQLAccount

4. Now that we created the SPN for the SQL Service we must allow it to delegate. Using Active Directory Users and Computers go to the properties of the account the SQL Service is running under.

There should be a DELEGATION tab there, if there isn't then the SPN was not set correctly (or not done at all) and you need to repeat step 2 and 3.

In the Delegation tab you will want to select "Trust this user for delegation to any service (Kerberos only)

5. Last step is to restart the SQL Service that can be done in the services section on the server. This will re-authenticate the user to the domain and it will get the new delegation changes.

6. Retry the BULK INSERT command from step 1 and it should work now.

Additional Information

Here are the resources used to solve the problem:

Windows 2003 Resource Kit (includes SPN)

http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en <blocked::http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en>

MSDN Article on using SETSPN and the Delegation Issue

http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx <blocked::http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/default.aspx>

TechNet Article on Allowing an User to be Delegated

http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true <blocked::http://technet2.microsoft.com/WindowsServer/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx?mfr=true>