Changing Authentication mode of connecting to Integration Services

  • In Managment studio(SQL SERVER 2008),I am trying to connect to Integration Services.But it is always in windows Authetication and I can not chnage it to SQL authentication .My login is sysadmin, but when I am trying to connect in window Authentication it says access denied.

    1-How is it possible to change the authentication mode to SQL Authentication of cnnecting to SQL SERVER 2008 Intgration Services?

    2-why my login (sysadmin) can not access to Integration Services and How can I give this permission to my user?

    Thanks

  • me too!

  • Aspet Golestanian Namagerdi (10/25/2009)


    In Managment studio(SQL SERVER 2008),I am trying to connect to Integration Services.But it is always in windows Authetication and I can not chnage it to SQL authentication .My login is sysadmin, but when I am trying to connect in window Authentication it says access denied.

    1-How is it possible to change the authentication mode to SQL Authentication of cnnecting to SQL SERVER 2008 Intgration Services?

    2-why my login (sysadmin) can not access to Integration Services and How can I give this permission to my user?

    Thanks

    If your SQL Server is Windows authentication you cannot change authentication at the point of connection, you must configure mixed authentication before trying to connect. In SQL Server at the top right click and go to server properties under security change to both SQL Server and Windows authentication and you can use SQL authentication to connect to SSIS.

    Kind regards,
    Gift Peddie

  • Thx for reply,But the SQL Server is on "SQL SERVER and Windows Authentication mode",but still I can chnage it during connecting to Integration Services.

  • Aspet Golestanian Namagerdi (10/26/2009)


    Thx for reply,But the SQL Server is on "SQL SERVER and Windows Authentication mode",but still I can chnage it during connecting to Integration Services.

    If SQL Server is in both Windows and SQL Server mode you should be able to use SQL Server authentication if you cannot use it then the system admin account is disabled when you installed SQL Server with Windows authentication so you need to enable it. Check the links below for details and how to enable it.

    http://msdn.microsoft.com/en-us/library/ms188670.aspx

    http://blog.sqlauthority.com/2008/12/24/sql-server-disable-and-enable-user-sa/

    Kind regards,
    Gift Peddie

  • Thanks alot for th replys.

    I guess I found the answer.Please refer to the link

    http://technet.microsoft.com/en-us/library/ms180068.aspx

    but How can I give this permission(Connecting to intigration Services) to a user

  • Aspet Golestanian Namagerdi (10/26/2009)


    Thanks alot for th replys.

    I guess I found the answer.Please refer to the link

    http://technet.microsoft.com/en-us/library/ms180068.aspx

    but How can I give this permission(Connecting to intigration Services) to a user

    That is related to connection within SQL Server Management Studio but if you are connecting in BIDs the development tool you can change authentication mode as needed.

    Which is default depends if your data source is SQL Server or other RDBMS.

    Kind regards,
    Gift Peddie

  • I know,My question is related to Managment studio environment.Now,how can I give the right of access to "Integration Service" to an windows account.Actually this will solve my problem.

    Thanks

  • Aspet Golestanian Namagerdi (10/26/2009)


    I know,My question is related to Managment studio environment.Now,how can I give the right of access to "Integration Service" to an windows account.Actually this will solve my problem.

    Thanks

    It will only solve the problem if mixed mode is not used to develop packages in BIDs you have to remember that because these issues can take a long time to resolve if the connection to the base problem is not addressed.

    Kind regards,
    Gift Peddie

  • Thx,But How can I give the right of access to "Integration Service" to an windows account

  • Aspet Golestanian Namagerdi (10/26/2009)


    Thx,But How can I give the right of access to "Integration Service" to an windows account

    The access to SSIS you give a Windows account will be based on what you define when you added the account to SQL Server in server login and database permissions. You connect to SSIS with existing Windows and SQL Server permissions not just Windows, what I am saying is to give Windows account SSIS permissions you must also add the account to SQL Server and MSDB database.

    Kind regards,
    Gift Peddie

  • Which roles should I assign to that user?

  • Aspet Golestanian Namagerdi (10/26/2009)


    Which roles should I assign to that user?

    I am sorry for the delay the answer is it depends on what your user will be doing, if your user will move data from IBM DB2 AS400 through jobs that is using MSDB with SQL Server Agent the user must be admin since SQL Server 2005. If you don't make the person admin then you need to have an admin account that will implement the automation part of the task because the code will not run. The way to do that is to use the Agent proxy account, run a search in the BOL for more about proxy account admin account required is not documented but I have a thread you can look to verify that information.

    Kind regards,
    Gift Peddie

  • Hi guys,

    I know that this post is very old, but I´m facing the same issue. Does anybody solves it?

    Kind Regards

    Paul Hernández

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply