Connect to remote instance of SSIS from SSMS using SQL Login?

I have installed a SQL Server 2005 Standard Edition instance with SSIS on Window Server 2003 x64. From my PC, I am able to connect to the Integration Services on the server using Windows Authentication but not with a local SQL login - and the option to change authentication methods is greyed out.

Is it possible to remotely connect to SSIS using a SQL login rather than a Windows login? If so, any ideas or references for configuring it as such?

Thanks.

- Lance

Per the post linked below, it's not supported.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=316143&SiteID=1

Connect To Oracle 10g 64 Bit using SQL Server Integration Services 2005 64 Bit

I am trying to create a oracle 10g connection in SSIS using connection manager and i am getting the following error,

Test Connection failed because of an error in initializing provider. Oracle client and networking

components were not found. These components are supplied by Oracle corporation and are part of the

Oracle version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.

Can someone please help.

Thanks.

If your operating System and VS2005 are 32bits then you need to download and install the Oracle 10g client for Windows 32bits, when I last downloaded it was more than 1gig install it and create your connection to the database make sure your TNS ORA works before adding SSIS connection to Oracle. The client tools are free but you need an Oracle Technet account which you can create for free. The Developer is also free it let you see Oracle tables and databases. If you are in an employer's network I find connecting with Named Pipes is easier than TCP/IP. One more thing watch the installer. Hope this helps.

http://www.oracle.com/technology/software/index.html

I forgot to add you must compile your code with AnyCPU.

|||

Thanks for your reply Caddre..

But.. i have MS 2003 64 bit OS.. i have installed oracle 10g 64 bit server.. i can successfully connect thru sqlplus.. no problem...

I am missing something on SQL 2005 and i am not sure what it is...

|||

So you are saying you have installed Oracle 10g 64bits client tools in the box? I know connecting through SQL plus does not solve development connection issue you need the client tools in your box this time 64bits. Another thing you are not missing anything but I think VS2005 unless you buy 64bits full version is 32bits that is not all your setup is 64bits, so you need to either make it all 64bits or develop in 32bits and deploy in 64bits because I read there are issues known issues.

|||

yes - as far as i know all are 64bit

I cannot connect thru both providers - OLEDB and Net Oracle client

|||

Try this recent Microsoft support article steps, I will still look for more info.

http://support.microsoft.com/kb/255084

|||

Hi Caddre,

Either you can have a look at summarized article here. or You may look for "Getting SSIS Packages To Run On A 64 Bit Box" in Sutha Thiru's Blog .

Thanks

Subhash Subramanyam

|||

thanks subash.. awesome..

those articles helped me to fix the issue...

appreciate yours and caddre help on this..

|||I am glad to see your problem is resolved and Subhash thanks for the articles much appreciated.

Connect to object explorer but cannot connect to Integration services.

I try to import my SSIS packages which are created using the Business Intelligent studio into SQL server 2005 but I cannot do that.

I am running SQL server 2005 enterprise edition.

Also when i tried to connect to Integration services using Object explorer, it only prompt me for server name. I cannot change the username and password which is disabled.

Then how am i supposed to connect to Integration services on a server in this case?

There is no username and password required to connect to SSIS Server.

-Jamie

|||

i got a server A...i tried to connect without password and user it gives me error....

the sql server is using sql authetication not windows authetication.

|||SQL Server authentication is for SQL Server. The SSIS Server (service) is something else and does not support SQL Server auth. You need windows auth to connect. It actually uses DCOM.|||

so how should i proceed from here?

my server wont allow me to connect to SSIS services using object explorer.

"Access is denied" connect to server 1 on machine failed.

Do i need to create anything on the server?

sqlsql

Connect SQLRPS2005 to SQL2k DB?

How do I go about connecting SQLRPS2005 in SPS3.0 integration mode to
a SQL2k DB?
(Actually I want to report from a Project server 2003 instance, which
is based on sql2k.
I have SQLRPS2k5 installed on the sharepoint server and have lots of
green ticks for the installation. I have also installed the SPS3.0
plug-in and cofigured its paramters through SPS central.
Then I create a data source within my SPS report libary with:
Data source type: Microsoft SQL Server
connection string: data source=act01projsvr01;initial
catalog=ProjectServer
Credentials: windows integrated
However, when I try to create a report builder model using this data
source I get:
Cannot create a connection to data source 'EPM Pilot.rsds'.
(rsErrorOpeningConnection)
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
So obviously the current users windows credentials are not getting
passed through to the remote SQL server correctly?
Any ideas?It looks like double hop issue. Real (and hard) solution is to use Kerberos.
As workaround, you can try to use stored credentials instead of Windows
Integrated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scubaal" <al@.blakes.net> wrote in message
news:1173848679.463141.29360@.n59g2000hsh.googlegroups.com...
> How do I go about connecting SQLRPS2005 in SPS3.0 integration mode to
> a SQL2k DB?
> (Actually I want to report from a Project server 2003 instance, which
> is based on sql2k.
> I have SQLRPS2k5 installed on the sharepoint server and have lots of
> green ticks for the installation. I have also installed the SPS3.0
> plug-in and cofigured its paramters through SPS central.
> Then I create a data source within my SPS report libary with:
> Data source type: Microsoft SQL Server
> connection string: data source=act01projsvr01;initial
> catalog=ProjectServer
> Credentials: windows integrated
> However, when I try to create a report builder model using this data
> source I get:
> Cannot create a connection to data source 'EPM Pilot.rsds'.
> (rsErrorOpeningConnection)
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> So obviously the current users windows credentials are not getting
> passed through to the remote SQL server correctly?
> Any ideas?
>

Configure Report Server - SharePoint Integration

Hello,

I'm trying to set up SharePoint Integration for our report server. The setup we have is: 1. SQL server hosting the SharePoint databases and the Reporting Server, 2. SharePoint web front end hosting all SharePoint services. I've installed the reporting services SP2 on the SQL server and the reporting services add-in on the SharePoint server.

I configured the report server following the directions here: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm and here: http://www.sharepointblogs.com/helloitsliam/archive/2007/02/21/19801.aspx

Everything seems to be set up correctly, but when I open the Reporting Services Configuration Manager, the SharePoint Integration option shows the error, "The report server cannot access setting in the SharePoint configuration database. Most likely, the Windows SharePoint Services object model...."

I do have WSS 3.0 installed on the report server.

When I look in the logs on the report server it says the login failed for the account I have set as the Windows Service Identity however, when I look at the logs on the SharePoint server it shows that it is granting the account read and write access to each of the SPWebApplications I have set up.

Any suggestions?

Thanks,

Cynthia

I just figured it out. It always happens that after days of unsuccessful attempts, I figure out the problem as soon as I post to a forum.

In case anyone else has the same problem:

In the Reporting Services Configuration Manager, under Windows Service Identity, I re-entered the name and password of the account and this caused a new dialog box to open prompting me to create an encryption key back-up. I provided a password and file location and then it worked. I'm not sure why I didn't get that dialog box initially, but everything works fine now.

Thanks,

Cynthia

|||

Hi

Can you people please confirm me that

Is that SQL Reporting Services 2005 can only be integrated with MOSS but not WSS 3.0

I am using wss3.0 (Not MOSS) and SQL reporting services 2005.

Configuration steps completed but i am not able to see the Reporting Services section in Application management

of Central Administration.

I installed SQL SP2 and Reporting Services Add ins and everything is on one system, i mean SQL2005 and wss3.0 on one system

I could use SQL Report configuration tool and set all the properties but not able to find Reporting Services section in Application management

of Central Administration.

Please Advice

Thanks,

Tiger

|||

Yes i got the solution for this

We need to Activate the Reporting services in the Central Administration this can be done by the following steps

1. Go to Site settings

2.In the Site collection Administration navigate to Site collection Features

3. Enable the Settings for Reporting Sevices

This will dispay the Reporting Section in the Application Management of Central Administration

Hope this will be help full.

Many Thanks,

Tiger

Configure Report Server - SharePoint Integration

Hello,

I'm trying to set up SharePoint Integration for our report server. The setup we have is: 1. SQL server hosting the SharePoint databases and the Reporting Server, 2. SharePoint web front end hosting all SharePoint services. I've installed the reporting services SP2 on the SQL server and the reporting services add-in on the SharePoint server.

I configured the report server following the directions here: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm and here: http://www.sharepointblogs.com/helloitsliam/archive/2007/02/21/19801.aspx

Everything seems to be set up correctly, but when I open the Reporting Services Configuration Manager, the SharePoint Integration option shows the error, "The report server cannot access setting in the SharePoint configuration database. Most likely, the Windows SharePoint Services object model...."

I do have WSS 3.0 installed on the report server.

When I look in the logs on the report server it says the login failed for the account I have set as the Windows Service Identity however, when I look at the logs on the SharePoint server it shows that it is granting the account read and write access to each of the SPWebApplications I have set up.

Any suggestions?

Thanks,

Cynthia

I just figured it out. It always happens that after days of unsuccessful attempts, I figure out the problem as soon as I post to a forum.

In case anyone else has the same problem:

In the Reporting Services Configuration Manager, under Windows Service Identity, I re-entered the name and password of the account and this caused a new dialog box to open prompting me to create an encryption key back-up. I provided a password and file location and then it worked. I'm not sure why I didn't get that dialog box initially, but everything works fine now.

Thanks,

Cynthia

|||

Hi

Can you people please confirm me that

Is that SQL Reporting Services 2005 can only be integrated with MOSS but not WSS 3.0

I am using wss3.0 (Not MOSS) and SQL reporting services 2005.

Configuration steps completed but i am not able to see the Reporting Services section in Application management

of Central Administration.

I installed SQL SP2 and Reporting Services Add ins and everything is on one system, i mean SQL2005 and wss3.0 on one system

I could use SQL Report configuration tool and set all the properties but not able to find Reporting Services section in Application management

of Central Administration.

Please Advice

Thanks,

Tiger

|||

Yes i got the solution for this

We need to Activate the Reporting services in the Central Administration this can be done by the following steps

1. Go to Site settings

2.In the Site collection Administration navigate to Site collection Features

3. Enable the Settings for Reporting Sevices

This will dispay the Reporting Section in the Application Management of Central Administration

Hope this will be help full.

Many Thanks,

Tiger