Hi,
i created a SSIS package to import data to a remote sql server, using the following connection string:
"Data Source=My-SQL;User ID=PortalUser;Password=Password;Initial Catalog=TestOMIWarehouse;Provider=SQLNCLI.1;Auto Translate=True;"
;and got an error "Login failed".
When i give PortalUser a sysadmin server role, it works. But i don't want to give PortalUser a sysadmin role. Any suggestions Is sysadmin role required to access a remote server using Sql server authentication
thanks! Any help would be appreciated.
alea
using SSIS package to connect a remote sql server--login failed.
Deicide
i tyied "Provider=SQLOLEDB.1;" annd got the same error message.
No. I am not using the SSIS connection string directly since I don't have access to remote sql server from my development machine. I hard coded connection string in a asp.net application and then deployed the application to a web server that has access to the remote sql server.
For testing, i right clicked the package and modified the connection managers and ran it.
and I have to use sql server authenication too.
thanks!
mohamed mwafy
Is PortalUser a user in the TestOMIWarehouse databases, as well as login at the server level
Just for comparison I would try using OLE-DB instead of SQL Native, but that is personal because I am less confident with it, not having used it as much.
Baloodevil
Should work, but try the .1 in case.
Data Source=Server\Instance;Initial Catalog=DBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;
Are you not using a SSIS connection directly Don't hack the connection string, as the UI makes less mistakes!.
tarzan123_us
Yes, PortalUser a user in the TestOMIWarehouse databases, as well as login at the server level.
I just tried using "provider=sqloledb" and got the following error "Class not registered".
Thanks!