All posts by cseferlis

Unable to deploy package to ssis 2014 Catalog

Recently ran into trouble because our Active Directory environment was upgraded to 2012, and I could no longer deploy packages to our SQL Server 2014 SSIS Catalog due to the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate

“deploy_project_internal”:

System.ComponentModel.Win32Exception: A required privilege is not held by the client

System.ComponentModel.Win32Excepbon:

at Microsoft. SqlServer. IntegrationServices.Server.ISServerProcess.StartProcess(Soolean

bSuspendThread)

at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectlnternal(SqlInt64 deployld,

SqIInt64 versionld, SqlInt64 projectld, SqlString projectName)

. (Microsoft SQL Server, Error: 6522)

I found a nice blog post from Archana, a Microsoft SQL Developer, that helped resolve the issue, but was a bit wordy with the solution.

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2013/08/20/system-componentmodel-win32exception-a-required-privilege-is-not-held-by-the-client-while-deploying-ssis-project.aspx

Long and short of the article was to follow the steps outlined below for the user account that needs access, and then run gpupdate on the server in question:

Odd authentication behavior

Just recently I came across some odd behavior when trying to connect to my SQL 2012 server via and SSIS package and ultimately SSMS from an external connection.  I’ll cut to the chase and tell you that the error I was receiving was the following:

The target principal name is incorrect. Cannot generate SSPI context. (.Net SqlClient Data Provider)

As it turned out, something went awry with the trust connection between the SQL server and the Active Directory Domain Controller that was not allowing my user account to authenticate.  Simply going into SQL Server Configuration Manager and re-entering the user password for the various services resolved the issue, however, not so simply, it took me about 7 hours of tweaking to figure out that was the problem…

The odd thing about this is that I was working in my environment on a Tuesday with no issues, connecting externally, etc.  Wednesday I was traveling for work, and upon my return on Thursday, any external connections I tried resulted in the error above.  The really strange thing about the error is that I could connect locally just fine, and I would only get it for external connections.

I went through the normal steps to check for external connectivity i.e.:

  1. Reboot (Fortunately, it’s just a server used for reporting, and not our SAP installation)
  2. Make sure SQL Browser and other services were running
  3. image
  4. Check the windows firewall (did an external port scan to make sure 1433 was open)
  5. Check the SQL Server Network Configuration protocols were enabled
  6. image
  7. Make sure external connections were enabled in Database Properties
  8. image

Even after all these steps and more, I simply could not find an answer, so I turned it over to my Sys Admin for another set of eyes, and almost immediately he found it by changing the “Log on as” account to local system.  This wouldn’t solve my problem completely, because I would need access to network shares within the domain, however, it got me pointed in the right direction, and viola! I was back in.

As it turns out, we have been recently doing some work on our domain as they put Win Server 2003 to End of Life, and upgraded to Windows Server 2012 Active Directory.  This helped me to remember that even when you think everything is working and configured the way it should be, there are always situations where something that’s happening externally to your own “silo” can still affect what you’re doing.  Always check with the Sys Admins and bounce the question of team mates when you run into issues.

SSRS 2008R2 Parameterized Reports stop working

SSRS 2008R2 Parameterized Reports suddenly stop working

After a recent round of Windows Updates, one of my SQL Server Reporting Server 2008 R2 users complained that reports were not working properly.  After some digging, I realized that in-fact, some of the reports were actually working, it just turned out to be the ones that require parameters.  The error looks like this:

image

The first thing I noticed was the “Error” message at the bottom that didn’t yield much information:

image

Although it got me pointed in the direction of something wrong with the Web Server.  In SSRS 2008, Microsoft changed the infrastructure and design of how the reporting services interacts with the different components and also eliminated the need for IIS.  Stacia Misner does a good job explaining this here:

http://sqlskills.com/BLOGS/STACIA/post/SQL-Server-2008-Reporting-Services-Configuration-Changes.aspx

By getting this information it made me realize that there was clearly no reason to continue messing with IIS to try to resolve the issue, so I began searching for the error I found in the log of SQL Server Reporting Services at:

C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLREPORTING\Reporting Services\LogFiles

The error that I found was:

System.Web.HttpException: The URL-encoded form data is not valid. —> System.InvalidOperationException: Operation is not valid due to the current state of the object.

And finally after an extensive search, I found this posting in MSDN:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/cb6ede72-6ed1-4379-9d3c-847c11b75b32

Looks like there was a Windows Update (KB2656351) that I had installed on Jan 12, 2012 and Possibly another (KB2656356) which did not affect me.  I uninstalled the first KB and rebooted, however, it did not resolve my issue.  By reading through the posting, I attempted the other suggested fix by modifying the web.config file found here:

C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLREPORTING\Reporting Services\ReportManager

Inside the <appSettings> tags:

<add key=”aspnet:MaxHttpCollectionKeys” value=”10000″ />

10000 is just a suggestion, you can set it to the value of your liking depending on the sizes of your parameters.

And my problem was resolved!