GP 10 Excel 2007 data connection security

[Vacation's Over]

Just implemented GP 10 and Excel 2007.

We deployed the Excel reports on a public folder for all users to see.

My question is: How do I control security access by user.

So far I can only refresh data if I use the sa username and password.

Thanks

[Imad Rafiq]

Post by Vacation's Over
Just implemented GP 10 and Excel 2007.
We deployed the Excel reports on a public folder for all users to see.
My question is: How do I control security access by user.
So far I can only refresh data if I use the sa username and password.
Thanks

I am having the same problem. I cannot figure out how to refresh data without hard quoting the sa user and password into the excel file. Please let me know if you have found a solution.

[Blair Christensen]

Use the following code to use your Windows authentication instead of database authentication:

Sub CloseDB()

cn.Close
Set cmd = Nothing
Set rs = Nothing
Set cn = Nothing
End Sub
Sub OpenDB()

cn.Provider = "sqloledb"
provStr = "Server=[servername];Database=[database];Trusted_Connection=yes"
cn.Open provStr
Set cmd.ActiveConnection = cn 'use cmd for stored procedures
cmd.CommandType = adCmdText
End Sub

We use this in quite a few legacy Excel reports as we try to transition these to SSRS. You'll need to set up a group on the domain. Next set up the group as a database user with the appropriate access rights to the various procedures used to generate the reports Then simply add the users to the group and you're ready to go.