October 25, 2009 at 1:24 pm
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
October 26, 2009 at 7:07 am
me too!
October 26, 2009 at 8:32 am
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
October 26, 2009 at 9:13 am
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.
October 26, 2009 at 9:59 am
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
October 26, 2009 at 1:31 pm
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
October 26, 2009 at 1:52 pm
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
October 26, 2009 at 2:03 pm
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
October 26, 2009 at 2:13 pm
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
October 26, 2009 at 3:35 pm
Thx,But How can I give the right of access to "Integration Service" to an windows account
October 26, 2009 at 3:50 pm
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
October 26, 2009 at 4:15 pm
Which roles should I assign to that user?
October 27, 2009 at 10:27 am
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
September 24, 2012 at 8:28 am
Hi guys,
I know that this post is very old, but I´m facing the same issue. Does anybody solves it?
Kind Regards
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply