Wednesday, September 14, 2016

Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server ""

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 4
Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "<Linked_Server>"

I was setting up a linked server to an Oracle database and I was able to connect from that server using the sqlplus client. So the client and all the hostname/port information was correct. Even the login was working just fine so was not sure what the 'Access denied' error is related to. I didn't see any message in the alert log on the Oracle server. So as usual after a couple of quick google searches I found the solution.

To resolve this go under Security in Management studio go to Server Objects > Linked Servers ? Providers

Right click on 'OraOLEDB.Oracle' and go to General. There enable 'Allow inprocess' and this should fix the problem.


Friday, September 9, 2016

Login failed for user xxxxxxxxx. (MsDtsSrvr)

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer.  For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2012 Books online

The developer today was trying to create a new folder in MSDB under the 'Stored Packages' in Integration services. However, he was getting the following error.


On this server we already had a named instance and Integration Services installed. I had recently created another instance for this developer to use and it looked like when he was trying to create the folder in the MSDB folder he was hitting the wrong database instance.

After some Google searches, I found some support pages that were related to this error.
https://msdn.microsoft.com/en-us/library/ms137789.aspx
https://indepthsql.wordpress.com/2012/03/15/ssis-error-the-sql-server-instance-specified-in-ssis-service-configuration-is-not-present-or-is-not-available-this-might-occur-when-there-is-no-default-instance-of-sql-server-on-the-computer-for-m/

The first step was to find this file called MsDtsSrvr.ini.xml. So I right clicked on Integration Services in SQL Server Configuration Manager and looked at the 'Service' tab. There you will find the 'Binary Path' for the Integration Services. You should be able to find that file under that directory. If not just do a file search.

It is typically located in %ProgramFiles%\Microsoft SQL Server\xxx\DTS\Binn. But depending on where you install the binaries this path will change. If you have a named instance then it will be different.

Edit this file in notepad and look for the following lines.

<Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.\NamedInstance</ServerName>
    </Folder>

The item highlighted in Red will be the Database Instance under whose MSDB Database the package is being stored. I found that the packages were being stored in the first named instance that was created on this server. Next I checked the SQL Server error logs and I saw login failed messages related to this developer in the older instance. This confirmed the problem.

There are two solutions to this problem.
1) Edit this file and enter the name of the instance to which this user already has access to.     
     This would be the new instance that was created.
2) Grant this user full permissions on the MSDB database in the older named instance. 

There are pros and cons for both. I decided to edit this file and point to the new instance since no one was using the Integration services and the old instance together. I guess the previous DBA installed the Integration Service when it was not going to be used for long.