Cannot set a credential for principal ‘sa’. (Microsoft SQL Server, Error:15535)

In this Quick and dirty post, let’s see a simple fix for one of the annoying error messages you’ll see in SQL Server 2005. Basically, You’ll see below mentioned error in few cases when you try to change the sa password using SSMS.

The Simple trick is to check the box “Map to credential” as shown below after providing a new password and click Okay.

This should work…Hope this helps!

 

 

 

Advertisements

Could not load package “\File System\Test” because of error 0x80070002.

Well, today I had a very funny/annoying/frustrating Issue where I was getting the below Error message when I tried to Run a Package from SSMS connecting to SSIS.

TITLE: SSIS Execution Properties
——————————
Could not load package “\File System\Test” because of error 0x80070002.
Description: Could not find file ‘C:\Program Files\Microsoft SQL Server\110\DTS\Binn\..\Packages\Test.dtsx’.
Source: mscorlib

Basically, I got that SSIS Package from a developer to Deploy on the Server and we do File System Deployments here in my Company. Before deploying the package onto Server and scheduling via Agent, I was asked to manually Run the Package and test it. Well, I was  successfully able to run that Package Just by Double Clicking and invoking DTExecUtil.exe. Everything is fine upto this Point. When I tried to Import the Package onto SSIS Server and run that package I was getting this Annoying and frustrating Errors…

Let me recreate the issue and show you what I’m trying to explain…

Step 1: I’ve a Package called “Test” in my Default Package Store as you can see below.

Step 2: I select Run package from SSMS as you can see below.

Step 3: Now, I click on Execute Button. and I get the Error Messages which I’m talking about!

Step 4: Now, Let me double click on the SSIS Package from the NTFS Folder path. I’ll get the same Execute Package Utility where I have an option to execute the package, but this time I’m able to Run the Package without any issues. So what’s really happening here???

Initially I thought it might be because of Permission Issues but that’s not the case.  I changed the Package Store Location and Restarted SSIS Service, but it didn’t helped me either..What’s happening really?? I was really frustrated at this point and Of Course  Hot Coffee made my mind to work Sharp 😀

If you observe the below Screenshot very carefully, there is a space between the file name and .dtsx!!!!

As you can see It’s “Test .dtsx” not “Test.dtsx“…ha ha! Yup…This was the Issue. Once I removed that Extra space between File name and Extension SSMS and SSIS both were happy as shown below!!

So the Problem was that single stupid Extra Space and I was like “Reallllyyyyyyyyyyyy??” It caused me 20 Minutes of my time….Sounds really Stupid right….?? But trust me folks, it was one crazy and Funny Error.

Bottom Line : SSIS doesn’t like Spaces 😉

Hope this helps when you get into any similar Issue 🙂 Cheers!

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable. “.

Couple of days ago we’ve seen how to fix DCOM permissions issue while connecting to SSIS remotely. See here.

In this Blog Post let’s see one more annoying error(RPC Server unavailable) we see developers complaining about, while connecting to SSIS 2008 Server from there client machines.

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “The RPC server is unavailable.
“.This error occurs when the computer host name does not exist, or cannot be reached.

I’ve SSIS Service running on “mirrorstand”  machine and I’m trying to connect from another machine “node2”. Please note, I’ve not configured Firewall yet on my “Mirrorstand” machine(Windows Server 2008).

How to Fix this Error?
All we’ve to do is add couple of exceptions in our Firewall, basically to allow “MsDtsSrvr.exe” Program and SSIS Port(TCP 135). Let’s see how to do that in action.

Step 1: Logon to your SSIS Server and Open Firewall with Advanced Services and create a new Inbound Rule for TCP Port 135.

Step 2: Click on New Rule and select Port as shown below.

Step 3: Select TCP and enter 135 as port number as shown below.

Step 4: 

Step 5: 

Step 6: 

Now, The most important part comes here. We’ve to add MsDtsSrvr.exe as an exception. Create a new Rule and follow the same screenshots, but You’ve to choose Program instead of Port and browse to the executable.

Typically, you should be able to locate this executable in “C:\Program Files\Microsoft SQL Server\100\DTS\Binn” Folder as shown below.

And, my firewall Inbound Rule looks like below…

Once I added the Port and Program to my Inbound Rules on my SSIS Server, I’m able to successfully connect to SSIS(mirrorstand) from “Node2” machine as you can see below… 🙂

Hope this info helps when you get into this situation…Cheers!

 

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied

In this Blog post let’s see how to fix DCOM Permissions issue for an user who receives below error when trying to connect to “SSIS” Remotely from a remote machine.

The Error Message you’ll receive when you are not granted required DCOM permissions is..

Connecting to the Integration Services service on the computer “mirrorstand” failed with the following error: “Access is denied.
“.

This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.

Note: The SSIS Server which I’m trying to connect is “MirrorStand”. The login which I’m trying to connect is ‘SREE\HR1″

So, how to fix this Error?

Solution:

Step 1: Log on to SSIS Server and Go to Component Services in Administrative Tools as shown below.

Step2: Navigate to DCOM Config ->MsDtsServer100 as shown below.

Step 3: Right Click on MsDtsServer100 and go to Properties and navigate to “Security” tab as shown below.

Step 4: Click “EDIT” beside all the three Options(Launch and Activation Permissions, Access Permissions and Configuration Permissions) shown in the above screenshot…

Once you click on ‘Edit’ button you will get the below screen where you’ve to add the required login as shown below.

Once adding in DCOM – component services is completed, then navigate to “Users and Groups” in computer Management and add the login in “Distributed Com Users” Windows Group as shown below.

Once you are done with adding the above shown DCOM permissions, you have to Restart SSIS Service. Then the login(user) should be able to connect to SSIS remotely.

As you can see below in the screenshot, I’m able to connect to SSIS Remotely (from a different machine, “Node1” in my case ) as ‘Hr1’ Login.

Tadaaaaaaaaaaaaaaa……..We made it 🙂

Hope this Helps!

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

Today I was setting up a Linked Server from one SQL Server 2008R2 Instance to another SQL Server 2005 Instance with one of the Databases as a Catalog and I was able to create the Linked Server and Tested it successfully, but the Developer was getting an annoying Error as shown below…

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

What does it mean? It is complaining about a missing permission from Resource Database, which really can’t be touched by any means in terms of Permissions from our SSMS. ( Remember, Resource DB can be accessed just from File System as mdf and ldf files from your Binaries Folder)

So, how to fix this annoying Error?

My Scenario:

1. I’ve a created  a Linked Server using “Microsoft OLEDB Provider for SQL Server” as  Provider by choosing Server type as Other Provider  instead of going with SQL Server. Don’t ask me why 😀

2. I’ve logged in as a user which is not a SysAdmin and when i try to expand the Catalogs from Linked Server I got  this below error message, as you can see in below Screenshot.

Please note I’ve logged in using login “Linked” – I’ve created a test login called “Linked” for this Demo Purpose…

How to Fix this?

1.Map the User to MASTER Database( By Def. It will be added to Public Role)

2.USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN;

Once, I did this, I’m able to access the Linked Server without any Issues as you can see below…

Howdyyyyyyy! Hope this helps

The file ‘microsoft.reportviewer.winforms version 9.0.0.0’ cannot be opened Error!!

In this Blogpost, Actually I thought of showing few hidden gems in our our Fabulous SSMS standard Reports, but ran into one of the interesting issues when i was trying to open REPORTS from SSMS. You can see the error Message below in the window.

Please note, I’m running on SQL Server 2008 SP2! So it has nothing to do with the Bugs or Service Packs or even any Cumulative updates installed!

How to Fix this Issue in my case?

I remember not Installing complete Client tools on this Server while I was installing SQL Server. You can see the below Screenshot where BIDS and other Client Components(I Installed only SSMS initially) being missing on this Server.

Let’s Install BIDS(Business Intelligence Development Studio) and see what SSMS say when we try to bring up Reports!

As you can see once I Installed BIDS, I was able to open Reports Successfully 🙂 Also, you can verify a folder called “ReportViewer” Being created in Microsoft Visual Studio Folder in “program files”  Directory as seen below.

Hope this helps when you get into a similar Issue!

How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.

Have you ever been in a situation where you lost the complete Drive where your System Databases are residing and even SAN Admins were not able to bring the Drive back?? Fortunately I’ve never been in that situation:)  What would be our available options if that disaster strikes?? In this scenario restoring System Databases won’t work because SQL Server Service itself is completely down and your System Databases mdf and ldf files are gone!!..So what should we do?? REBUILD YOUR MASTER. Rebuilding Master will actually create brand new System databases from scratch as if you just Installed your SQL Server.  There’s a misconception that we need the Actual SQL Server media and it’s mandatory to rebuild your Master. Those days are gone..That was the case with SQL Server 2005 and earlier. Starting SQL Server 2008 it’ll rebuild based upon the TEMPLATES which it creates in your BINN Directory. (This is the one of the main Reasons,you should place your SQL Binaries and actual System Databases on completely separate Physical Drives.  Let me show you what I’m talking about…..You can see the templates folder which SQL Server created while initial Installation of this Instance on my machine.

These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below

Note: On My laptop I Installed every thing Just on C Drive. You should never ever do this in a real environment. There’s no single valid/good reason to do so…

Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.

I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.

Step1: Now my Data folder looks as shown below

Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).

In the Event Viewer I’m seeing the below

Okay…! In this case we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.

Step 3: Let’s see how to rebuild using SETUP.EXE 

As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below

Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can find SETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”sreekanthPC\sreekanth” /SAPWD=”Pa$$w0rd”

If you want to explore all other options and parameters available to us….you can issue “setup.exe/?”  for help from windows as shown below.

In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).

Very Very Important: Actually It failed for me and got below error Messages!!!! The error Messages were “The following is an exception stack listing the exceptions in outermost to innermost order  Inner exceptions are being indented  Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.”  I’ve no idea what it is trying to convey here…Looks like a Bug( may be may be not) to me in SQL Server 2008R2( I’m using 2008R2 for this demo).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe

Even if this doesn’t work for you, please try to make use of setup.exe from Installation media. This should work. I hope this strange behavior has been fixed in DENALI.

Sooo…..the actual Syntax and Path which worked for my case is as shown below

it took around 7-10 minutes in my case and got a command prompt without any errors as you can see in the above screenshot 🙂

FYI Failed path(in my case) as per Microsoft documentation  is as shown below

Please keep this Bug in your mind and don’t just PANIC if you encounter the same error message.

Well, with all this hard work…what did we achieved?? Did SQL Server Instance “DR” got rebuilt??  Yesssssssssss as you can see below, all the System database files are back.

Also..I was able to Start SQL Services from the Configuration manager and as you can see below….everything appears to be brand new…. Tadaaaaaaaaaaaaaaaaaaaaaaa!! We did it guys 🙂 🙂

 

Now it’t time to (patch if applicable) and  restore your Databases from your healthy Backups accordingly. You can see how to restore System Databases here.

That’s it Guys!!! You should be aware of this process and these challenges and work arounds as a production DBA. I would recommend to test this in your lab at least once or twice before actual disaster strikes your Production Servers…(I hope you never need this….But you should not be surprised when it hits). Hope this helps. Cheers!