August 25, 2021 at 10:02 am
Hi
I am trying to create a SSIS package run on a Server Agent job.
I have created a user in SSMS that I think has the correct access to the correct databases yet i am getting a login failed in both the job and in the SSIS package in visual studio.
Can anyone point me in the right direction please.
Thanks
August 25, 2021 at 10:29 am
Can you post the complete error message and obfuscate the servername/loginname etc.
Has the driver you are attempting to use been registered on the SQL server and all client machines where Visual Studio is used for this package along with ensuring the runtime has been set to 32bit or 64bit correctly?
Have you configured the job/logins/proxies etc up to handle using a different account other than the SQLAgent service account?
August 25, 2021 at 10:33 am
If you check the SQL Server's logs you'll see the full authentication error. What is that error? That'll tell us why the LOGIN/USER is failing to connect. Errors passed to applications, from SQL Server, are intentionally vague, but the reason the connection fails could be many, such as (to name a few):
LOGIN nameLOGIN does not have a USER mapped in the database being connected to.LOGIN does not have connect permissionUSER credentials for contained databaseThe error in the logs will expose what the real reason is.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2021 at 11:18 am
Thanks a lot for the replies
details from the log
08/25/2021 11:05:39,Logon,Unknown,Login failed for user 'xxx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: ]
08/25/2021 11:05:39,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
August 25, 2021 at 11:26 am
Thanks a lot for the replies
details from the log
08/25/2021 11:05:39,Logon,Unknown,Login failed for user 'xxx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: ] 08/25/2021 11:05:39,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 58.
Well, that error is pretty specific. You're clearly trying to use a SQL Authentication Login, but your server is set up to to only accept Windows Authentication Logins; as a result the connection is denied.
You'll need to enable SQL Authentication first, see Change server authentication mode, and then restart the instance. Presumably you have already created the SQL Server login, so the connection should then work.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2021 at 11:53 am
What about the error in the logs? As I explained before, the errors to the application aren't helpful.
If you're getting the same error in the logs, you didn't restart the instance after changing the setting.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2021 at 12:05 pm
It is saying the password did not match that for the login provided
it does
any idea why it would work for a short time?
August 25, 2021 at 12:29 pm
It is saying the password did not match that for the login provided
it does
any idea why it would work for a short time?
If the error is saying it does, it doesn't. Passwords are case sensitive, so perhaps you have a lowercase letter where it should be upper, or you had caps lock in in error.
The password disappears in the SSIS connection after i close the window
That would suggest you aren't saving the changes in the dialogue window; such as by clicking "cancel" instead of "OK". In truth, however, I can't recall the last time I used SQL Authentication on SSIS, so it might be that it blanks the Password Box out for them. It doesn't in SSRS though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2021 at 12:37 pm
Hi, I am saving the changes, it seems to be a common problem
Thanks again for your help
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply