asked
SSIS - OLE DB Connection with Mirrored Databases.
I am working on creating a scheduled job with SSIS (using Visual Studio 2008). SLQ server environment: Principal server (on line databases) Mirror server (databases in Restoring mode) Witness server, with automatic failover. (all SQL server 2008) Problem: In the Connection Manager, when trying to create a New OLE DB Connection, I select the server name (which is the Mirror server) and then in the drop down for "Select or enter a database name" I do not see ANY mirrored database from the server name I selected. I try to enter the database name manually and when clicking on Test Connection, I get an error: "Test connection failed because of an error in initializing provider. Login failed for user 'user_name'. Cannot open database "(data_base name)" requested by the login. The login failed." I do not get this when mapping the server name to the Principal server with the online databases. That works just fine. I know that mirrored database in Restoring mode cannot be access, but is there a way to map the mirrored database to the OLE DB connection? Any advice is greatly appreciated (I am a newbie to SSIS).
when a database is restored , the users will also been restored from the primay server.we have to do user mapping for getting connectvity for the databases.using exec sp_change_users_login 'update one','user name' point 2) you can't get access to database which is in restoration mode or no recovery mode
3 People are following this question.
