July 7, 2016 at 8:34 am
I have a number of SSIS packages (package deployment model) running on their own ETL server. I am trying to modify several of them to use the always on secondary via the LISTENER, but having trouble when I try to debug the package. Especially if I am using SQL Server security for the login.
Most of the searches I've done, only seem to discuss the SSISDB catalog, which is not what I'm after, since I want to be using the secondary to retrieve production information.
It seems to work fine (adding the applicationIntent, sufficient timeouts) if I use integrated security, but when I use the SQL Server security, it fails to log in to the listener. It also doesn't matter if I use OLE DB or ADODB, both fail. Using Windows security to connect defeats the purpose of protecting my data based on SQL userid and roles.
I'm using Visual Studio 2013, and SQL Server 2014. If anyone has been successful at this, I'd like to know what kind of hoops were jumped through.
Thanks,
Luther
July 7, 2016 at 9:27 am
A bit of a newbie question, but have you confirmed that the SQL Server instance is configured to use mixed mode authentication?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 7, 2016 at 9:42 am
a double check with the DBA's confirms we are supporting mixed mode authentication.
July 11, 2016 at 9:28 am
latkinson (7/7/2016)
a double check with the DBA's confirms we are supporting mixed mode authentication.
Security the same for the account you are trying to use on both sides of the AG? You can't have it on one server and not the other.
July 11, 2016 at 9:39 am
same security.
I am thinking its my connection string, that I am trying to store it in the package configuration, but exactly what is off, is the thing I can't quite figure out.
Anyway, if you think of something let me know, otherwise, I will keep experimenting every few days until I get it working.
July 11, 2016 at 9:51 am
I just reread your initial post and have a question.
Would you please expand on what you mean by this:
Using Windows security to connect defeats the purpose of protecting my data based on SQL userid and roles.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2016 at 10:17 am
By windows security, I mean active directory. We use sqlserver logons so that the userid remains consistent whether in development, staging, or production. That doesn't happen using the Windows/active directory security.
July 11, 2016 at 11:57 am
latkinson (7/11/2016)
By windows security, I mean active directory. We use sqlserver logons so that the userid remains consistent whether in development, staging, or production. That doesn't happen using the Windows/active directory security.
I must be misunderstanding you: my Windows Id remains the same, regardless of the SQL instance to which I am connecting.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2016 at 12:34 pm
we tie our database connections to specific SQL Server userid's for various security and auditing reasons.
July 11, 2016 at 12:57 pm
latkinson (7/11/2016)
we tie our database connections to specific SQL Server userid's for various security and auditing reasons.
For my own information, please give me an example of a security- or audit-related thing you can do with a specific SQL Server user Id which cannot be done using a Windows login.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply