PowerShell PSSecurityException: UnauthorizedAccess

I just fought with this for close to an hour and the solution was REALLY simple so I thought I'd try and save someone else an hour of their life.

When trying to execute an unsigned PowerShell script (yeah let's not go there) from a VSTS release job I encountered the following error:

2018-04-30T20:35:34.8886378Z ##[error]. : AuthorizationManager check failed.

At line:1 char:3

+ . '\\exec-mgr-build\vsts-rel\SSISProjectWeaver-CI\38\deploy_ssis_proj ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : SecurityError: (:) [], PSSecurityException

+ FullyQualifiedErrorId : UnauthorizedAccess

2018-04-30T20:35:34.9115615Z ##[error]Process completed with exit code 0 and had 1 error(s) written to the error stream.

I was able to workaround the error by using the local path (this wouldn't have worked if the PowerShell script was on another server). Even though I had a workaround I wanted to be able to use a UNC path. Turns out all you have to do is add the path as a Trusted Site. You can do this within Internet Explorer's security options like so:

Before I spend the rest of my day trying to explain why and how this works I am going to cut it short and get back to finishing my release definition. Hope this helps!

Visual Studio SQL Server Project (SSDT) OPENQUERY Warning

Using OPENQUERY in a stored procedure in a Visual Studio SQL Server project will result in the following warning.

 SQL70558: The column definition from this OPENQUERY function cannot be verified, and the operations involved with it might not be understood correctly.       

Depending on how your project and build is configured, this warning it may or may not fail the build. Regardless, if you have to use OPENQUERY and don't want the warning, you can use the Suppress TSql Warnings property on the stored procedure. To view this property in Visual Studio, click on the .sql file in the Solution Explorer window. You should now see this property in the last row of the Properties window. Enter the 70558 warning number. The warning should now be suppressed and will no longer appear in the Error window or the build output.

The Suppress TSql Warnings property can be used to suppress other warnings as well. To suppress multiple warnings just delimit them with a comma.

RapidMiner jTDS Network error: IOException: Connection refused: connect

Network error: IOException: Connection refused: connect

There a quite a number of reasons why you might get this error. However, assuming the following:

1. You're using jTDS to connect to connect to a SQL Server instance 
2. You're trying to use Windows authentication (not passing SQL Server credentials)
3. You've installed the ntlmauth.dll library (link to download page). 

Confirm the following via the SQL Server Configuration manager (Start->Run->SQLServerManager11.msc):

1.  The TCP/IP protocol is enabled.

2.  Your SQL Server instance is listening on the SQL Server default port of 1433 -or- you have entered the correct port in your jTDS configuration/connection string.

3.  The SQL Server Browser service is running.

If you confirmed and/or changed your configuration based on the above recommendations, restarted your SQL Server instance, and still cannot successfully connect, refer to the jTDS SourceForge FAQ page.

The feature: "Shared dataset" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Ouch. I was recently working on a project that started with SQL Server 2008 R2 Standard Edition and eventually ended up with SQL Server 2012 Web Edition in a hosted environment. When I attempted to stand up a staging environment on the 2012 Web Edition instance I received the following error.

The feature: "Shared dataset" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

I came to find out that the "Shared component library" feature of SSRS is only available in Standard Edition and up. I have to admit that before we moved from 2008 R2 to 2012 I reviewed the Features Supported by the Editions of SQL Server 2012 MSDN page and didn't realize that the Shared Dataset feature was a subset of the Shared component library feature. It appears that this feature is also sometimes referred to as the "Report Part Gallery" as can be seen on the Programming Features for Reporting Services Editions page, also on MSDN.

SSDT: unresolved reference to object [dbo].[sp_executesql].

Procedure: Some_Procedure has an unresolved reference to object [dbo].[sp_executesql].

Look familiar? There's a simple solution. We just need to add a reference to the master database.

1) Right-click the References folder in Solution Explorer.

2) Select the "master" System database.

3) Voila. The reference appears under the folder and the warning goes away.

The Excel Connection Manager is not supported in the 64-bit version of SSIS

When attempting to execute a package that makes use of an Excel Connection Manager you may be presented with the following errors.

• SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "YourConnectionMgrName" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
• [Connection manager "YourConnectionMgrName" Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

The problem is that Microsoft doesn't provide a 64-bit Excel Connect Manager component (as stated in the error). This means that to run the package it must be run using the 32-bit DTEXEC.exe. When running in BIDS (Visual Studio) it's as simple as setting a project property. Below is the default property on a 64-bit machine. Change True to False to run the package.

If you're running the package under a SQL Server Agent job you must also set a similar property on the job step that runs the package. Notice the Use 32 bit runtime property. Make sure this is checked.