SQL Server – BulkCopy (Bcp) – Error – Unexpected EOF encountered in BCP data-file

Background

Having problems copying data using bulkcopy.

Error Message

bcp dbo.tblThirdPartyRooms in C:\Users\daniel\AppDdbo.tblThirdPartyRooms_v1.data -E -T -c -b20000 -S.\SQLEXPRESS_V2014 

Starting copy... SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file 0 rows copied.
Network packet size (bytes): 4096 Clock Time (ms.) Total : 15

What is the problem?

Let us make sure that structurally the tables are the same.

To do so, we can try using tablediff

TableDiff

 

Syntax

Item Explanation Sample
 Binary  Application Name  C:\Program Files\Microsoft SQL Server\120\COM\tablediff.exe
 -sourceServer  Source Server  SeattleDB
 -sourcedatabase  Source Database  Hotel
 -sourceschema  Source Schema  dbo
 -sourcetable  Source Table  tblThirdPartyRoomMapping
 -f Name of SQL file that will bring destination database object inline with source object %temp%\alignSQL.sql
 -o  BCP Output file %temp% \bcpOutput.txt
 -q  Quick Row Count a) Do not list individual record differences
b) Quicker

 

 

Sample

 

SETLOCAL

	set TABLEDIFF_BIN="C:\Program Files\Microsoft SQL Server\120\COM\tablediff.exe"
	set fixSQLFile=%TEMP%\alignSQL.sql
	set BCPOutputFile=%TEMP%\BCPOutput.txt

	rem Source
	set ss=SeattleDB
	set sd=Hotel
	set ss2=dbo
	set st=tblThirdPartyRoomMapping

	rem Destination
	set ds=.\SQLEXPRESS_V2014
	set dd=Hotel
	set ds2=dbo
	set dt=tblThirdPartyRoomMapping

	rem Misc Options
	set miscOptions=-f %fixSQLFile% -o %BCPOutputFile% -q 

        rem reset ERRORLEVEL FLAG
        verify >nul
	%TABLEDIFF_BIN% -sourceServer %ss% -sourcedatabase %sd% -sourceschema %ss2% -sourcetable %st% ^
     	-destinationserver %ds% -destinationdatabase %dd% -destinationschema %ds2% -destinationtable %dt% ^
		%miscOptions% 

       rem display errorlevel
       echo ErrorLevel is %ErrorLevel%
ENDLOCAL

Output:

Here is our run output …

TableDiffOutput
Error Level:
ErrorLevel

 

And, here is what we discovered upon inspecting our BCPOutput.txt file.

BCPOutput

Quick Explanation:

  • We were able to connect to both servers
  • As we expected, we have data in the Source Database, but not in the Destination database
  • We were hoping that we will find schema differences, but none showed up

 

Compare BCP Out to BCP Input Command

Went back and reviewed our BCP Commands

BCP Output


bcp Hotel.dbo.[tblThirdPartyRoomMapping] out %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data  -t"||" -T -c -S%DBSourceServer%

 

BCP Input


bcp %DBTargetDB%.dbo.tblThirdPartyRoomMapping in %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data -E -T -c -b%BATCH_SIZE% -S%DBTargetServer%

Upon a more careful comparison of the BCP Export and Import statements, now noticed that we have a column delimeter in the export, but we missed it on the input.

BCP Input (Corrected)


rem please notice the -t"||" -- adding delimeter
bcp %DBTargetDB%.dbo.tblThirdPartyRoomMapping in %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data  -t"||" -E -T -c -b%BATCH_SIZE% -S%DBTargetServer%

Summary

Once again, this is just a stupid error on my part.

Wrote it up as the initial hits that came up via Google, were a bit muddled.

I suppose no one makes mistakes like this.

Southeastern Baptist Theological Seminary

Southeastern Baptist Theological Seminary

Summary

In his sermon, Payday Someday, Tony Merida led me to a very well written book, 1st and 2nd book, by Peter J. Leithart.

TheInnocentScapegoat_small_v2

Alycia Woods – Problem of Truth

Background

Had a 2 hour long conversation with a friend last night.

Offhandedly, I asked her how work is going.  She delved into how hard the week has been.  And, I feel sorry for asking, as she revealed a bit too much.

See, she is a special need teacher in Southern California.  And, in her classroom the children have to be mechanically fed and changed.

She takes long journeys to help High School students get into College, get financial AID, etc.

She works hard off hours to visit homes with 10 people sharing a room and trying to get their child into Head Start programs so that a new mother can go back to work.

And, yet many times, she finds herself trying much more than the beneficiaries on the other end.

 

Problem of Truth

Alycia Woods is with Ravi Zacharias International Ministry ( RZIM ).

Here is her take on the Problem of Truth.

https://www.youtube.com/watch?v=ItlDji5k9Fo&list=PLOW2qj_SjYN0SrQAdjXH12qUtAeK6EJVp&index=3

 

Microsoft – Outlook – Inactive Application Add-Ins

Background

My manager is out on vacation and so I find myself free loafing once again.

Rather than doing my assigned work, I am busy raiding our Support Desk Ticketing System for problem calls.

 

Fancy

The ones that catches my fancy are a couple of Microsoft Outlook Add-In ones.

Quite a few Inactive Add-Ins:

OutlookAddIns-Inactive

 

One of them is about one of our 3rd Party Add-Ins not being loaded because of security concerns.

The specific error message is “Not loaded. Certificate of signed and load at startup Com Add-In is not trusted source List.”

CertificateOfSIgnedisNotTrusted

 

 

Trouble Shooting

Checked everywhere for the root cause.

  • Checked the referenced DLLs to see if it is signed
    • If so, wanted to add the Certificate Authority to trusted CA list
  • Outlook \ Trust Center \ Trusted Publishers
    • From Outlook, reviewed my list of Trusted Publishers

But, nothing obvious.

 

Solution

The problem ended up being a bit of hyper sensitive setting for Macros.

To see if it yours:

  • Launch Outlook
  • Access menu items File \ Options
  • In the “Outlook Options” window
    • On the left panel, access “Trust Center”
    • On the right panel, access “Trust Center Settings”
    • In “Trust Center”
      • On the left panel, access “Macro Settings”
      • In the “Add-Ins” panel, we found that he is gated for “Apply macro security settings to installed add-ins”
      • To fix, uncheck that setting

Before:

Outlook-TrustCenter-MacroSettings-ApplyMacroSettingsToInstallAddIns

 

After:

ApplyMacroSecuritySettingsOff

Listening

Macklemore & Ryan Lewis – Same Love Feat. Tegan and Sara
https://www.youtube.com/watch?v=UPHG5n-DBBw

SQL Server – BulkCopy (BCP) – Identity Columns

Background

I am copying data from Production to my personal Development box, but my SQL Join Queries are not yielding any data.

Code

Original Code


SETLOCAL

 set DBTargetServer=.\SQLEXPRESS_V2014
 set DBTargetDB=Dev
 set BATCH_SIZE=30000

 sqlcmd -e -b -Q"truncate table dbo.tblStaffingDateTime" -S%DBTargetServer% -d%DBTargetDB%

 timer.exe

   bcp %DBTargetDB%.dbo.tblStaffingDateTime in %TEMP%\dbo.tblStaffingDateTime.data -T -n -b%BATCH_SIZE% -S%DBTargetServer%

 timer.exe /S

ENDLOCAL 

 

Revised Code

Traced the problem back to ensuring that we preserve our Identity Values.

When issuing bcp, we use -E to insist that our supplied identify values are used, rather than for the system to generate its own.


SETLOCAL

 set DBTargetServer=.\SQLEXPRESS_V2014
 set DBTargetDB=Dev
 set BATCH_SIZE=30000

 sqlcmd -e -b -Q"truncate table dbo.tblStaffingDateTime" -S%DBTargetServer% -d%DBTargetDB%

 timer.exe

   rem -E Keep Identity Values ON
   bcp %DBTargetDB%.dbo.tblStaffingDateTime in %TEMP%\dbo.tblStaffingDateTime.data -T -n -E -b%BATCH_SIZE% -S%DBTargetServer%

 timer.exe /S

ENDLOCAL 

Metrics

Using Gammadyne Timer Utility ( http://www.gammadyne.com/cmdline.htm#timer ) compared how long it takes to insert 150 thousand records.

 -E Skipped :- Does not Preserve Identity Values
keepIdentityOffCleaned

 -E Added :- Preserve Identity Values

keepIdentityOnCleaned

 

Conclusively, when we preserve our Identity Column our throughout is about 15% less.

Dedicated

Will like to go another way with this.  But, got to claim my own.  And, this morning it is Duke Blue Devils,  the National Collegiate Basketball Champion 2014.

But, even then it is April.  And, yet still considered going another road, Dave Loggins’ Pieces of April.

But, will rest with Touch-Of-Pennsylvania ( https://www.youtube.com/watch?v=9_sQv2hfVxg )

Automatic Web Proxy Discovery and Client Configuration in MS Windows Environment

 

Background

For most of us that work in Corporate MS Windows Environment, our Internet Gateway \ Proxy configuration is pretty hidden.

At home, we either have direct connections to the Internet, have a gateway assigned to us by our ISP, or get on the Internet through our own Router or Wireless Access Point (WAP).

 

Corporate Environment

On the other hand, while at work in a Corporate Environment, when we do the following:

  • Access Control Panel
  • Access Internet Options
  • In the “Internet Properties” window, access the “Connections” tab
  • Within the “Local Access Networks (LAN) settings” group box, click the “LAN Settings” button
  • In the “Local Access Network (LAN) settings” window, you will be able to review your Proxy settings

Our available choices are

    • Automatically detect settings
    • Use automatic configuration script
    • Use a single Proxy Server
    • Access to configure proxy server based on traffic type ( HTTP/FTP, etc)

 

InternetProperties-LocalAreaNetworkSettings-AutomaticConfiguration-AutomaticallyDetectSettings

 

Inquiry Mind

So to put it subtly an Inquiry mind wants to know.  Which server is proxy-ing our web traffic.

Well that is where WPAD comes in?

 

Honorable Mentions

Richard Hicks

His article “Configuring Web Proxy Automatic Discovery (WPAD) in Forefront Threat Management Gateway (TMG) 2010” knocks the topic out of the park.  It stimulates my thinking, and notice that I did not say it stimulated my thinking.

Richard is a Microsoft’s Enterprise Security MVP; and he does that acknowledgement well.

 

Web Proxy Automatic Discovery

There are a couple of ways that a machine acquires its Internet’s Client Configuration

  • DHCP
    • For machines that do not have fixed IP Addresses, the network’s DHCP server can return the Internet Proxy Server as part of the initial Network Configuration configuration.  That is, when returning other Client Configuration data such as the Assigned IP Address, Gateway Address, and Subnet mask.
    • As Richard’s article pointed out, for bigger networks with a stable of Proxy Servers, we are able to designate specific Proxy Servers on subnet basis.  That is we specify Proxy Server T1 for Building A, and another Proxy Server, Proxy T1, for Building D.
  • DNS
    • DNS Server Configuration
      • Create “A” DNS records for each Proxy Server
      • Create “C” records that point to the various “A” records.
      • The C records will bear the name WPAD
    • Client Configuration/Requests
      • DNS Clients issues requests for WPAD
      • The DNS Server will return the IP Address for one of the “A” records

Which One are we using?

DHCP

I honestly can not say for sure whether we are getting Internet proxy configuration via DHCP.

I will have to use a Network Traffic Tool and review its requests and the response from the DHCP to answer affirmatively.

DNS

But, I can say that we are using DNS; solely or in addition

Query DNS for WPAD Records

  • Access Console
  • Issue DNS Query


Query Syntax

nslookup WPAD

wpad

 

Explanation:

When we use nslookup and issue WPAD query against our default DNS Server, we get back

  • DNS Server
    • Server :- The name of the responding DNS Server
    • Its IP Address
  • WPAD
    • Name :- The name of the WPAD Server
    • Addresses :- The A records IP Addresses
    • Aliases :- The “C” records

Network Connections

Let us review our current network connections and see if we indeed have traffic going to our stated Proxy Server: 

Syntax:
netstat -anb | find [Proxy-Server]
Sample:
netstat -anb | find "10.4."

 

Image:
netstat--anb

 

Application

Configuration

Most Web Browsers have been coded to able to work with WPAD.

On the other hand, 3rd Vendors might not have augmented their applications likewise.

 

Notepad++

Here we configure Notepad++ with our Proxy Server, its IP Address and Port Number.  And, later our Network username and password.

Plugin Manager Settings

PluginManagerSettings

Proxy Credentials:

ProxyCredential

 

 

Installation Failure

But, sadly our installation of our plugin failed.

InstallationError-InstallationOfPowerShellLexerFailed

 

 

Why you ask me:

To determine why our install failed, we have to dig a bit deeper and see if there are log files created by Notepad++.

Notepad++ developers are good ones and you know they are smart.

Here is where Update Log files are and where they are not:

  • C:\Program Files (x86)\Notepad++\plugins = No
  • C:\Users\[username]\AppData\Roaming\Notepad++\plugins\config\plugin_install_temp\plugin1 = Yes

Here is our Proxy Log:

installFailed

 

The identifying error is:

  • Error Code: 407 Proxy Authentication Required. Forefront TMG requires authorization to fulfill the request. Access to the Web Proxy filter is denied. (12209).

 

Conclusion:

  • Yes, I wish we had a WIN
  • But, our corporate security is a bit complex
    • We do not have traditional username/password, but smart badge and accompanying pin
    • Yes, I have Internet access for my regular user tied into the Smart Badge
    • But, it is very unlikely that I have Internet access on my Admin Account

 

Listening

Kenny Chesney & Kid Rock – LuckenBach Texas
https://www.youtube.com/watch?v=TjDmdiE-Bvg

At the end of the song their is an exchange between Kenny & Kid Rock; it reads

How they did it in 80 ….
If you listen to this song, you are listening to something real

Don’t listen to something else
At 4:00 O’Clock in the morning

Thank God, they get to stay up till 4 O’Clock in the morning, doing what they are happy doing.

And, as for me, thank Goodness for Richard Hicks and other MVPS.

I will take the advice of the singers here; as I am unlikely to listen to anyone else.

 

References

Security Vendors

SQL Server Agent – Proxy Account

Background

We are developing some utilization reports.  Though, the reports and the accompanying programmable objects are not nearly ready for Production deployment, we need to get our hands on realistic and more recent data from our Production environment.

And, so what we have decided to do is prepare a SQL Server Agent job that runs on a periodic schedule and downloads more recent data from the Production DB unto our Development database.

Why this Post?

In a SQL Server installation, there are two baseline accounts; the account the SQL Server Engine is running under; and the account that the SQL Server Agent is running under.

As a rough sketch, in most cases, the engine account is effectual.  And, the Agent account is only manifested when a job is running.  The Agent connects to the engine and acts as a surrogate to run a job.

In a heavily used SQL Instance, there could be need to delegate security and not have all activities be marshaled under the singular account of the lone SQL Agent.

For that purpose, we can  use Proxy Accounts.

Requirements

There are a couple of things that I know we need right off the bat.  And, those are :

  • A locked-down Active Directory Service Account
    • I personally don’t like the old classic native SQL Account with exposed passwords
    • I prefer AD Account locked down with a bit higher password requirements.  If abused or compromised, you can disable them in one place
    • They are also much easier to audit – determine when they were last used
    • Easier to follow and trace through your Network, as well
    • They can also be locked down to specific machines
  • Database Objects
    • List of database tables that we will be reporting on
    • Review the table sizes and presence of primary/distinguishing keys, timestamp /last update datetime
  • Refresh Type
    • Full refresh or incremental refresh?

 

Scripts

Here is a quick rundown on the scripts that we will be creating…

  • Linked Server Creation Script
    • This script creates a so called symbolic link to the other server; in this case our Production DB Server
  • Stored Procedure Script
    • This is  a Stored Procedure script that will contain our insert/delete/update/merge/BULK INSERT statements
  • SQL Server Agent Operator
    • This script will register the  list of interested parties that should be notified, in case of scheduled jobs failure
  • Database Job
    • The container job that will initiate the data transfer step and notification alert in case of failures

 

Provision Account

 

Let us grant our proxy account access to our Subscriber DB

Grant access to SQL Instance

use [master]
go
if SUSER_SID('LABDOMAIN\datarunner') is null
begin

     print 'Granting Account access to SQL Server ... ' 

     create login [LABDOMAIN\datarunner] from windows; 

     print 'Granted Account access to SQL Server'
end
else
begin
    print 'Account exists'
end
go

 

Grant Access to User Database


/*
    Grant access to specific database
*/
use [database]
go

if DATABASE_PRINCIPAL_ID('LABDOMAIN\dataRunner') is null
begin

    print 'Granting Account access to ' + db_name()

    create user [LABDOMAIN\dataRunner]
    from login [LABDOMAIN\dataRunner];

    print 'Granted Account access to ' + db_name()

end
else
begin

    print 'Account exists in '+ db_name()

end
go

 

Linked Server Creation Script

Using SQL Server Management Studio, we created the Linked Server:

Linked Server Properties – Security

LinkedServerProperties_Security

 

Linked Server Properties – Server Options

LinkedServerProperties_ServerOptions
Test the Connection

TestConnection

 

Transfer Script

Here is what our transfer script looks like.


set identity_insert on dbo.[customer] on;

declare @customerID bigint

set @customerID = isNull( 
                           select max([customerID])
                           from [PROD].[salesDB].[dbo].[customer]
                        ), 0)

insert into dbo.[customers]
([customerid], [customerName])
select [customerid], [customerName]
from  [PROD].[salesDB].[dbo].[customer]
where [customerID] > @customerID

set identity_insert on [dbo].[customers] off;

 

SQL Server Agent Operator

We have a script that creates a SQL Server Agent Operator account.

I will encourage that we beforehand  have created an email distribution list and use that distribution list;  thus we avoid needing to edit jobs as DBAs come and go.

NewOperator

 

Database Job

Job Step – General

JobStep-General

 

Job Step – Advanced

JobStep-Advanced

 

Explanation:

  • Notice there are two “Run as” drop-down fields
    • On the “General” page, the “Run as” is available when the “Job Step” is other than a “Transact-SQL Script (T-SQL)”
    • On the “Advanced” page, the “Run as user” is available when the Job Type is “Transact-SQL script (T-SQL)”

 

Execute Job

So everything is in place. We kicked off the job and got an error.

Here is the error:

 

Textual:

Executed as user: LABDOMAIN\datarunner. Access to the remote server is denied because the current security context is not trusted.
[SQLSTATE 42000] (Error 15274).  The step failed.

Image:

JobHistory

 

Remediation

The problem is that when we set a Job Step to execute through a proxy account, the system will subjugate the permission of the job’s owner,

and assume the permissions of the proxy account.

This can have system wide security ramifications and at day’s end, we will blame the wrong person, the running user; which for that step will be

the Proxy Account.

And, so we are being forced to vouch for the entire database or at minimum the SQL Step that we are running.

Notice that SQL Server CLR has the same requirement.

Again, the requirements are:

  • The database has to be marked trustworthy
  • or, the programmable object invoked should be signed

 

Easy Way Out

The easy way out for us, is to mark our Database as Trustworthy.


   alter database [database] set trustworthy on;

 

Errors

Review of errors we encountered and how we addressed each:

SQL Severity File Version Error Message Fix
 14 916 EXECUTE AS USER’ failed for the requested user ‘LABDOMAIN\datarunner’ in the database ‘HRDB’.  The step failed. Create SQL Server Login and Create individual databases users
16  15274  Executed as user: LABDOMAIN\datarunner. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed. Set referenced database to trustworthy

 

Additional Reading

Impersonation and Credentials for Connections
https://msdn.microsoft.com/en-us/library/ms131068.aspx

In the SQL Server common language runtime (CLR) integration, using Windows Authentication is complex, but is more secure than using SQL Server Authentication. When using Windows Authentication, keep in mind the following considerations.
By default, a SQL Server process that connects out to Windows acquires the security context of the SQL Server Windows service account. But it is possible to map a CLR function to a proxy identity, so that its outbound connections have a different security context than that of the Windows service account.

In some cases, you may want to impersonate the caller by using the SqlContext.WindowsIdentity property instead of running as the service account. The WindowsIdentity instance represents the identity of the client that invoked the calling code, and is only available when the client used Windows Authentication. After you have obtained the WindowsIdentity instance, you can call Impersonate to change the security token of the thread, and then open ADO.NET connections on behalf of the client.