PS2007 Impersonation using VBA (not PSI)

[darkstar]

I've been stuck on this for a few days now, so I hope someone here can
help

Basically, I would like to connect to the ProjectServer_Published
database (read only) and check the current user's credentials in order
to apply the appropriate filter when certain projects are opened in
Project 2007 Professional. I'm using the RES_CODE field in the
MSP_RESOURCES table to store the access level information. The
following code works fine when using my own (or another admin's)
logon:

Dim Conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim CurrentUser As String

CurrentUser = Environ("USERNAME")

'Create Connection
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Conn.ConnectionString = "Provider=sqloledb;" _
& "Data Source=SQL_Server\SQL_Instance;" _
& "Initial Catalog=ProjectServer_Published;" _
& "Integrated Security=SSPI;"
Conn.Open

'Open recordset
rs.Open "SELECT RES_CODE, RES_NAME FROM MSP_RESOURCES", Conn

and so on... The filter is then applied for the RES_NAME that matches
CurrentUser based on the value in the RES_CODE field.

Since the connection works only for users with datareader permissions,
for regular users I need to impersonate an Admin account in order to
run the SQL connection and query. Our SQL servers use Windows
authentication exclusively, so passing SQL user IDs and passwords in
the connection string is not an option.

I have learned that I need to use the LogonUser,
ImpersonateLoggedOnUser, RevertToSelf, etc. functions of the advanced
Windows API (advapi32.dll), but I haven't been able to construct the
proper combination of code to make it work. Sometimes I got the
following error:
"Can't find DLL entry point LogonUser in advapi32.dll"
and sometimes the SQL server rejects the connection because it won't
accept "anonymous logins" (even though the Admin user ID and password
is provided).

Does anyone have any suggestions (or code) to make impersonation work
in VBA? Thanks in advance for any and all help.

 

[Chris Boyd]

Hey,

Why not go to the resource PSI to get this information? That is a supported
programming interface and how to impersonate is documented in the SDK. I
highly recommand that you do not write applications that work directly
against the Publish DB. It may get broken after a hotfix or service pack
install.
--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/

 

[darkstar]

If I could, I would - we happen to have a very territorial (and
paranoid) IS department that is split into different factions, and I
know I will never get permission to try anything server-side. It was
hard enough just to get datareader access. I know that Microsoft
actively discourages writing apps that directly access the SQL data,
but is it so wrong to just READ from the sacred tables to get
something done? The function I'm developing is relatively trivial and
is intended to enhance the user experience - we can always train users
to apply the filters themselves if it should break, no big deal.
Besides, what are the chances of established database fields being
renamed as a result of a hotfix or service pack?

Thanks a lot for not helping.

 

[Chris Boyd]

The risk is there, that is one of the reasons why we have invested in the web
services. We can alter those tables in a service pack and there will be no
warning to the user that we have made these changes because it is not a
supported interface. I would suggest that if you cannot get your solution to
work with the PSIs, that you investigate a solution that uses the RDB. It is
a supported interface to develop against.

I am surprised you have issues accessing the web services since that is what
PWA and Project Client uses. If the IS department blocked your users from the
PSI, they would not be able to use PWA or Project Client with the server.

--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/

 

[Rod Gill]

Hi Chris,

I think a lot of organizations have this problem. Your average power user in
a project office or project server implementer implementer (people like me)
do not have C# or Web services skills and getting programmer support in many
organizations for an internal project can involve a 3 month wait just to get
the request in the first Queue. Personally I believe PSI is for development
of 3rd party add-ons by professional developers and not for end user
customers at all.

Now, if Microsoft developed some skins so the Web Services could easily be
consumed from VBA???!!!

I agree with you though, developing code to the Reporting databases is much
better, especially as the Views in the RDB that end with _userview have all
custom fields added to them automatically. Now that's a feature that really
helps us poor Project Server implementers!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[darkstar]

First of all, thanks to all for your input. The point about using the
Reporting database is well taken - a simple matter of connecting to it
and reading the ResourceCode and ResourceName values in the
MSP_EpmResource table. However, the impersonation problem remains, so
I guess the point is moot.

All this is really geared towards finding a way to restrict what users
can see and do in Project Professional - from all indications, it
seems like everything is wide open once a project is opened in Project
Pro. What I would dearly like to see is a way to extend the (very
good) security model that exists in PWA to the Project Professional
environment. Our projects tend to be made up of components (groups of
tasks) that belong to different departments, and we would like to be
able to restrict views and edits in Project Professional to those
items owned by the administrators of the different departments. Where
changes impact the schedule of tasks owned by others, we have
automatic email notifications sent to the affected owners when the
project is published. (Yes, we tried the master-subproject approach
and it was too unwieldy.)

I realize that this does not conform to the "project manager controls
all" doctrine, but this is the way we work.

 

[Rod Gill]

Hi,

There is no effective way of restricting any data in Project Professional.
It can be hidden "under the covers" but anyone with reasonable Project
Professional skills can see under the covers.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com