This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have an SSIS package and in that, I connect to SSISDB using the following connection string.
Data Source=ABC1234;User ID=TST_USR;Initial Catalog=SSISDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
The provider is Native OLEDB\SQL Server Native Client 11.0.
The UserId and password are passed to the connection. TST_USR is a SQL auth type login.
In a scenario like this, what will be the connection attempt?
I don't know on the top of my top. My gut reaction is Don't do that!
But if you want to know, add an Execute SQL task with SELECT SYSTEM_USER and see what you get back.
If you provide "Integrated Security=SSPI;" to the connection string, it will be using Integrated Security to login to SQL (with the current windows account or service account that you applied to run the application). It will ignore the username and password even if you provide them in the connection string.
Thanks @Ken Kam Hung, Lin The behaviour I experienced was different and explained in the reply to ZoeHui-MSFT.
Hi @Subash Basnayake,
The Integrated Security property instructs the SQL Client to connect to SQL Server using Windows Authentication through the Security Support Provider Interface (SSPI). Recognized values are true, false, yes, no, and sspi , which is equivalent to true.
When this property value is true or yes, the DRDA Service will connect to SQL Server using Windows Authentication.
When this property value is false or no, the DRDA Service will connect to SQL Server using SQL Server Authentication.
See Configuring SQL Server Connections
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
Hi @ZoeHui-MSFT ,
Thanks for the answer.
But my experience was different. This package was executed through an agent job and the agent job was running as a Windows auth service account. But that Windows auth account was not listed as a login in this SQL server.
However, this connection worked and the above conn string was extracted from the following SSIS log and it says the connection succeded. Also, the package is working as expected in this particular environment.
ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource()
ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
When asked in chatgpt the answer was the other way around. When a username/password is provided the SSPI part will be ignored. Since we can't rely on that I asked the question here. This was working fine in many instances and we recently noticed an issue when the datasource in the connection string is defined as "abcdev.xxxyyy.com,443". In this case, this was a publicly exposed name of the SQL server. This public domain was set to resolve internally too. But still, we got a login error for this connection string and in the logs, it seemed like it was attempting to log in using the Windows auth account which initiates the SSIS package. So we changed the connection string to use the internal IP address instead of the public one and then the connection started working with the SQL authentication with the SSPI parameter still in the conn string. I would like an explanation for this behaviour.
Based on my experience it seems like when sql auth username/password is provided the SSPI is ignored. But when the data source is defined as a domain name the SSPI comes into play and it attempts Windows auth.
When I test, I find that if I have both, it tries to use SQL authentication. But obviously, if there is a difference depending on how you specify the server, my simple testing most likely missed that. But I am curious how concluded that it tried with Windows Authentication? What did the error message say? And is there any particular reason that you have both in the connection string?