Changing SQL Server data file locations in clustered instance using service SIDs

Recently I added a second LUN to a clustered instance of SQL Server to isolate the logs files (and another for the TempDB) and I mounted them to an empty NTFS folder as I’ve done before, but when I started SQL Server, I recieved the following error:

CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
'X:\MSSQL10_50.INSTANCENAME\TempDB\tempdb.mdf

The error means that SQL Server doesn’t have NTFS rights to the location of the TempDB, but when I tried to add those rights, the permissions weren’t granted to the domain proxy account as I’d expected, but were instead granted to the service SID account, MSSQL$InstanceName. I attempted to grant the permissions to this account at the new location, but couldn’t get it to resolve to an actual account. What finally worked was:

  1. In the permissions change dialog box, change the “Location” from the domain to the local machine (even though it’s a cluster and your using a domain account to run the service)
  2. In the text box, type “NT Service\MSSQL$INSTANCENAME” and click “Check Names”

Even though that appears to be a local account, it will resolve properly on all the cluster nodes involved. This step, as opposed to using the domain proxy account the service is running as, was necessary because (during the initial SQL Server setup process) I’d selected to use the proxy account SID to host permissions rather than a domain group. The better choice permissions-wise, but the source of some confusion!

Launching Microsoft File Transfer Manager

In case you were in the middle of a download from MSDN or another Microsoft site that uses the transfer manager and you’ve accidentally closed it, you can find it at one of two locations:

If you installed it from an MSI:
x64 – C:\Program Files (x86)\Microsoft File Transfer Manager\TransferMgr.exe
x86 – C:\Program Files\Microsoft File Transfer Manager\TransferMgr.exe

Downloaded automatically in IE (more likely):
%SystemRoot%\Downloaded Program Files\TransferMgr.exe

Mine was hiding in that second location – if you download it directly in IE, it doesn’t create a start menu icon, so you’re not able to re-launch the tool unless you know the file location.

Dealing with an exception: “An attempt was made to access a socket in a way forbidden by its access permissions”

I was attempting to bind a remoting listening to a particular port and kept receiving an exception when attempting to bind on the production Windows 2008 R2 server itself – it always worked fine on both my development box and our test server. Here’s the exception:

Exception message: An attempt was made to access a socket in a way forbidden by its access permissions
Stack Trace:
   at System.Net.Sockets.Socket.DoBind(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.Bind(EndPoint localEP)
   at System.Net.Sockets.TcpListener.Start(Int32 backlog)
   at System.Runtime.Remoting.Channels.ExclusiveTcpListener.Start(Boolean exclusiveAddressUse)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerChannel.StartListening(Object data)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerChannel..ctor(IDictionary properties, IServerChannelSinkProvider sinkProvider, IAuthorizeRemotingConnection authorizeCallback)

Though Googling gave some suggestions to run the process as an administrator (no effect), the actual problem was that my process was trying to listen on a port that was already taken by another listener. Since the port was already in use, it couldn’t bind and I received the exception. Stopping the other process resolved the issue immediately.

Not really a clear error message, since it really had nothing to do with permissions at all, but there you go.