CODEWITHSUNDEEP
sql-serverauthenticationssis
Koosh
Koosh

Reputation: 896

SSIS cannot connect to SSMS using SQL Server Authentication

I'm trying to set up a connection to SSMS from SSIS project I'm working on with SQL Server Authentication.

I set up a cube_user login and gave it permission to my database transactions Here is how I set up my cube_user: Login Name, password. I also chose the default DB below to transactions, the appropriate DB.

enter image description here

Then in User Mapping tab, I gave user owner permissions and I checked in the appropriate DB

enter image description here

Having done all that, it seems like it should work. I then started an SSIS project and tried to connect to it like this:

enter image description here

As you can the drop down that is supposed to display databases, is blank. However, If i tried to connect to it using Windows Authentication, I am able to see all DBs....

enter image description here

Is there something wrong I'm doing. Can you not connect to SSMS using SQL Server Authentication?

Upvotes: 1

Views: 433

Answers (1)

Ferdipux
Ferdipux

Reputation: 5246

Please check the following things:

  • In properties of cube_user - that it has at least guest rights on the RDBMS (SQL Server itself). User rights on specific DB will not give the permission to connect to SQL Server. The properties are in SSMS - Security\Logins - specific Login properties - Server Roles. See example below
    enter image description here

  • On the third screenshot where you specify user name and password - specify those of cube_user, not admin.

The reason why you see list of DBs with Windows Auth - you do it under your account which presumably has DBA user rights on the SQL Server.

Upvotes: 2

Related Questions