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 ...