Here's the scenario that tripped us up during our SQL Server 2005 upgrade: 

Consider a stored procedure that executes a BULK INSERT command where the file in the FROM clause is located on a remote machine.  When executing this stored procedure using a trusted connection, i.e., Windows Authentication, the Windows account must be trusted for delegation in order for this stored procedure to be executed remotely.  However, based on my testing, the following scenarios will work:

1. Modify the stored procedure to BULK INSERT a local file, i.e., local to the SQL Server.  The stored procedure can be successfully executed either locally or remotely,
2. Execute the stored proceude that BULK INSERTs a remote file locally on the SQL Server machine.  In other words, the SQL Client and SQL Server machines must be one and the same.

For the most common client / server scenario (separate client and server machines with the BULK INSERT file located on a third server) the Windows account used to execute the stored procedure must be trusted for delegation. 

However, from a security perspective, using SQL authentication is probably the best solution ...

I'm not usually a big fan of screen savers - I always lock my desktop(s) before stepping away from my desk - but I've run into an excellent one here.  It's a simulation of a "blue screen of death" followed by a reboot - very realistic (right down to simulating disk activity) ...

Install this one and play a trick on your favorite network admin!  (But first make sure he has a sense of humor!)

Our recent conversion from SQL Server 2000 to SQL Server 2005 revealed a couple of issues with stored procedures that run BULK INSERT commands.  [These issues were not issues with under SQL Server 2000.]

1.  "Operating system error code 5(Access is Denied)" when attempting to execute this command using Windows Authentication.  This appears to be an impersonation / delegation issue that we believe we can fix with delegation.  [Stay tuned ...]

2.  Assorted format file errors "Invalid destination table column number ...", "Bulk load data conversion error (truncation)", etc.  We've been able to workaround these by increasing the column widths for some of the column definitions.

The moral of the story is to test everything that calls a BULK INSERT carefully as part of the upgrade process ...

This space has been a little quiet for a while now but I've been busy with a major SQL Server upgrade as well as architecting a generic job scheduling environment (and working with DataGridViews for our standard data entry screens) ...

Look for upcoming posts on the difficulties we've run into with our SQL Server upgrade ...

I ran into a situation on my SQL Server 2005 test machine where I wanted to swap database files that were installed on two separate instances of SQL Server 2005.  [One instance is the "default" instance, and one is named "Instance1".]  The database files are located in a parallel file structure like so:

D:\SQLData\MSSQL

D:\SQLData\MSSQL$INSTANCE1

I figured the easiest way to accomplish this is to detach the databases from both servers, shutdown the SQL Services, swap the names of the two directories, startup the SQL Services, and reattach the databases.  Upon reattaching the databases, I recieved a permissions failure ...

To fix this, I examined the file permissions of the data and log files.  In my case, I needed both the local system account and the appropriate SQL Server user account corresponding to the SQL Server instance [in my case, the account is named SQLServer2005MSSQLUser$<machine_name>$<instance_name>] to have Full Control.  [Swapping the names of the folders meant the data and log files permissions gave Full Control to the user associated with the other instance.]

All this assumes that the SQL Server instances run under the Local System account ...

Here's another way around the problem:  enable and start the SQL Server Browser service on the SQL Server 2005 machine.  This will allow you to connect remotely to non-default instances without specifying the port number ...

In our test environment, we install multiple instances of SQL Server 2005 on the same box.  If you are having trouble remotely connecting to your non-default instances, be sure to do the following:

1.  Make sure the TCP/IP protocol is enabled for your instance.  Use the SQL Server Configuration Manager (a SQL 2005 client tool) to do this.

2.  While you are using this tool, make note of dynamic TCP port that your instance is using.  SQL Server instances communicate on a single port.  The default port is 1433, which is what you get for the default instance, but for additional instances, a dynamic port is randomly selected for you at install.

3.  When remotely connecting to a SQL Server 2005 instance that is not the default instance, use the server name and port number.  For example, to connect to SERVER1\Instance5, enter "SERVER1\Instance5,<nnnn>", where <nnnn> is the dynamic port number of the specified instance.

At my shop, we've been looking into the source code control integration that's available for our database objects such as stored procedures, functions, triggers, etc.  Since we are in the progress of migrating from SQL Server 2000 to SQL Server 2005, we looked at the integration that's available betweem VSS 2005 and SQL Server Management Studio ...

In a nutshell, you can create a SQL Server Management Studio solution (*.ssmssln) that contains any number of SQL scripts (*.sql).  You have the option of scripting the entire database, including schema and code, in a single script, or scripting each object individually.  These scripts can then be managed by VSS 2005 as you would manage any piece of source code ...

The only thing lacking is the fact that you can't directly manage what's checked into VSS at the "source" level vs. what's compiled in SQL Server.  However, if your development team is disciplined enough to use SQL Server Management Studio as the sole tool for managing SQL scripts (and executing the scripts as they are checked in), then source code management of SQL Server objects can be achieved using the combination of SQL Server Management Studio and VSS 2005 ...

In lieu of installing a .NET application (including referenced assemblies) to an Intranet server or network share, you may be tempted to copy your assemblies to a network drive and execute them directly from there.  However, if you've ever tried to do this, your application probably threw a SecurityException ...

To get around this, you have a few options that are documented at the .Net Security Blog.  You could open up your security policy to fully trust the Intranet Zone, which is probably a bad idea.  Or, you could strongly-name each assembly and configure the appropriate permissions for each assembly that you are going to deploy, which is a better idea.  However, this means that you need to modify configuration settings on each user's machine every time a new assembly is deployed ...

To avoid this, you could configure a specific network folder (and subdirectories) to be fully trusted.  This way, all future assemblies can be deployed to this network folder without the need to individually configure each assembly.  Of course, you still open yourself up to rogue applictions that are installed in this folder being fully trusted, but you are more secure than if you fully trusted your entire Intranet!

To fully trust a specific network folder:

1. Open the .NET Framework 2.0 Configuration applet,
2. Expand the tree to My Computer, Runtime Security Policy, Machine, Code Groups, LocalIntranet_Zone,
3. Right-click New... to create a new code group,
4. Name your code group,
5. Choose URL as the condition type for this code group,
6. For the URL, enter a UNC path using the following format:  file://\\<network drive>\folder
7. On the next page of the wizard, select Full Trust as the existing permission set,
8. Click Finish to add the code group ...

To test that your assembly is now fully-trusted from the network path, right-click at the Runtime Security Policy node and choose Evaluate Assembly... .  If you navigate to your assembly, you can validate that your assembly is indeed fully-trusted.  Of course, you can always execute your assembly to validate that your assembly no longer throws a SecurityException, but using Evaluate Assembly... can come in handy when debugging security problems ...

I recently installed a hardware upgrade on my email server and was forced to reboot the machine.  [The machine is a year old or so, Socket 754 AMD64 processor, MSI K8T NEO-FIS2R motherboard.]  After the machine came back up, I noticed that the NIC driver (Realtek 8110S integrated NIC) did not load.  This had been working for the past 6 months or so (ever since I installed Windows Server 2003 on the machine) and has been used in a production environment without incident ...

Perplexed, I went rooting around for a driver for this NIC that supports Windows Server 2003 and could not find one specific to that operating system.  How in the world did I get it to work before ???

To make a long story short, I discovered that I had previously installed the Realtek 8169 driver, despite the warning that this driver did not match the hardware.  Installing this driver did the trick ...

I recently put together a new desktop machine for my home network environment, with the intention of installing the 64-bit version of SQL Server 2005.  Having not looked at the 64-bit versions of Windows Server 2003 and Windows XP in a while, I did a bit of research first to make sure all the 64-bit drivers were readily available.  I chose the Asus A8R-MVP motherboard (and an AMD64 dual-core processor) based on the availability of 64-bit drivers, plus the fact that this motherboard supports the newest, fastest SATA II hard drives ...

After completing the install, I was not disappointed in my choice.  I was able to install the operating system without having to download any additional drivers, and everything worked perfectly.  No problems with RAID drivers, network drivers, or audio drivers (although I admin I didn't plug in any speakers).  The 64-bit edition of SQL Server 2005 installed without incident as well ...

If you are looking to install the 64-bit version of either Windows Server 2003 or Windows XP on your hardware, here are two things to keep in mind:

1.  Make sure all the 64-bit drivers required for hard drives, network cards, sound cards, USB, Firewire, etc. are available for your hardware and for the operating system you choose ...

2.  Make sure you have all the software utilities that you need.  Low-level utilities that we've all become dependent on, such as Norton AntiVirus, certain DVD-burning software packages, etc. require you to obtain 64-bit versions of the software as the 32-bit versions will not work ...

As a continuation of my previous entry, here's a list of software tools and utilities that I find to be essential for any Microsoft architect, developer, and consultant:

1.  MSDN Subscription.  Here's where you get access to the latest Microsoft development tools, operating systems, servers, betas, etc.  Each month, you receive DVD updates.  In addition, you can download the latest updates anytime ...

2.  Antivirus and Anti-spyware software.  There's really not much to say here.  Keep your virus definition files updated regularly and use these religiously.  Practice "safe computing" ...

3.  Virtual PC.  I've blogged quite a few "tips and tricks" for using Virtual PC.  It is a valuable tool for testing, demos, and working with beta software.  Learn to use it and love it!

4.  Backup Software.  Don't wait for a disaster to happen before realizing that you don't have a backup of your "stuff"!  Even if you do, you may not realize how long it can take to recover.  Get a "ghosting" backup utility that does a sector-by-sector image of your hard drive and store the backups remotely on an external hard drive.  I purchased a product called R-DriveImage.  It allowed me to image my hard drive, install a new, larger hard drive, boot from a CD, and restore the image from an external hard drive within a matter of an hour! 

5.  Disk partitioning software.  You'll also need disk partitioning software such as Paragon Partition Manager to resize disk images ...

6.  NewSID utility.  This handy freeware utility is essential for working with ghosted images, virtual and otherwise.  It renames all computer and user SIDs on your image which allow you to deply multiple copies of the image on the same subnet without conflict ...

7.  DVD Burning software.  Essential for burning DVD images.  There are several good ones out there; I'm partial to Nero ...

8.  Isobuster.  This utility allows you to extract ISO CD and DVD images onto your hard drive.  This is useful for dealing with large DVD ISO images and Virtual PC ...

9.  RSS reader.  You'll want to keep abreast of key developments that others are blogging about.  I use RSSReader ...

10.  And, finally, blogging software.  Again, you can choose to host your own, outsource it entirely, or outsource the hosting, and deploy your own blogging software.  Mine is based on DasBlog ...

Have I forgotten anything ???