Windows NT Integrated Security

[chuck]

I have an Access ADP project which uses "Windows NT Integrated Security" to
connect to the SQL server. On occasion, me or some of my users may be
logged on to Windows with an account that does not have access to the SQL
database. Can I add code to Access that will prompt me for another set of
Windows credentials to logon to the database ?

thanks

 

[Douglas J. Steele]

Not as far as I'm aware.

 

[Chris2]

I have an Access ADP project which uses "Windows NT Integrated Security" to
connect to the SQL server. On occasion, me or some of my users may be
logged on to Windows with an account that does not have access to the SQL
database. Can I add code to Access that will prompt me for another set of
Windows credentials to logon to the database ?

thanks

chuck,

The following may, or may not, work.

You will have to open up your Visual Basic Editor (Tools > Macro > Visual Basic Editor).

In the VBE, you will have to open up the References dialog box, (Tools > References).

Scroll down until you locate the SQL-DMO reference. Check it and click ok. If it is not
there, you can't do this.

Once checked, open up SQL Server's Books Online and read up on SQL-DMO.

Look up the Login, User, and ServerRole objects and their associated collections.

-----------------

For Example (from BOL, Login Object):

- To add a login to a server running SQL Server

1 - Create a Login object.
2 - Set the Name property.
3 - Set the Type property. By default, a login is created for use by SQL Server
Authentication. Alternately specify the login type to map a Microsoft Windows NT® user or
group.
4 - Add the Login object to the Logins collection of a connected SQLServer object to
create the SQL Server login.

Note To view, create, or remove SQL Server logins by using the Login object, the connected
user must be a member of the SQL Server securityadmin fixed server role.

-----------------

That last requirement, securityadmin privileges, may be a problem.

I've never tried doing this, and so as noted above, I can't say how it will work out for
you.


Sincerely,

Chris O.

 

[chuck]

Thanks for the info. Not sure about the objects you specify but the
"connect" method does allow me to connect to a db programatically and select
Windows or SQL authentication. Unfortunately, there does not appear to be a
way to pass an alternate set of credentials with Windows authentication.
Maybe I can use Windows' "runas" utility instead and write an external
utility that will promt be for a username.

In any case, here is what MSDN states about SQL-DMO.

http://msdn2.microsoft.com/en-us/library/ms131540.aspx
SQL Server 2005 Books Online
SQL Distributed Management Objects (SQL-DMO)
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.

Chuck

 

[Chris2]

Thanks for the info. Not sure about the objects you specify but the
"connect" method does allow me to connect to a db programatically and select
Windows or SQL authentication. Unfortunately, there does not appear to be a
way to pass an alternate set of credentials with Windows authentication.
Maybe I can use Windows' "runas" utility instead and write an external
utility that will promt be for a username.

In any case, here is what MSDN states about SQL-DMO.

http://msdn2.microsoft.com/en-us/library/ms131540.aspx
SQL Server 2005 Books Online
SQL Distributed Management Objects (SQL-DMO)
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.

Chuck

Chuck,

I believe there is a new object model in SQL Server 2005 named SMO. Check the 2005 BOL to
see what the status of that is, http://technet.microsoft.com/en-us/library/ms162169.aspx.

I know less about SMO than SQL-DMO.

I found:

Managing Users, Roles, and Logins:
http://technet.microsoft.com/en-us/library/ms162144.aspx

Under, Programming Specific Tasks,
http://technet.microsoft.com/en-us/library/ms162175.aspx



Sincerely,

Chris O.