Brett Barabash
BBarabash at TappeConstruction.com
Fri Sep 17 14:29:46 CDT 2004
To all who use NT authentication:
Do you (or your network admins) have any concerns about end users
potentially linking to the raw data in other apps?
For example, a savvy power user could easily setup an ODBC connection in
Excel/MS Query and directly query the table data. If the database
relies on NT authentication, and the table is available to them, they
can link to the data directly.
I found this to be problemattic, since I don't really want my users
bypassing the business rules and manually generating their own reports
with sensitive data.
Christopher:
I had this problem a while back using SQL Server authentication on an
Access 2000 MDB file. The problem is that the Access attach tables
dialog doesn't save the password info. I was able to get around it by
writing the following DAO routine (make sure you have a DAO reference
set in your project). This routine will loop through all of your
attached tables, and relink them using the specified UserID and
Password.
Public Sub FixAttachments(ByVal strServer As String, ByVal strUID As
String, ByVal strPWD As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim strSQL As String
Set db = DBEngine.Workspaces(0).Databases(0)
strSQL = "SELECT MSysObjects.Name, " & _
"MSysObjects.ForeignName, " & _
"MSysObjects.Connect " & _
"FROM MSysObjects " & _
"WHERE MSysObjects.Type=4 " & _
"ORDER BY MSysObjects.Name;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
db.TableDefs.Refresh
Do Until rs.EOF
Debug.Print rs!Name
Set tdf = db.CreateTableDef(rs!Name)
tdf.Connect = "ODBC;" & _
ExtractArg(rs!Connect, "DRIVER") & ";" & _
"SERVER=" & strServer & ";" & _
ExtractArg(rs!Connect, "DATABASE") & ";" & _
"UID=" & strUID & ";" & _
"PWD=" & strPWD
tdf.Attributes = dbAttachSavePWD
tdf.SourceTableName = rs!ForeignName
db.TableDefs.Delete rs!Name
db.TableDefs.Append tdf
rs.MoveNext
Loop
db.TableDefs.Refresh
rs.Close
End Sub
Public Function ExtractArg(ByVal strIn As String, ByVal strName As
String) As String
Dim lngPos As Long
strIn = strIn & ";"
lngPos = InStr(strIn, strName & "=")
If lngPos > 0 Then
ExtractArg = Mid$(strIn, lngPos, InStr(lngPos, strIn, ";") -
lngPos)
End If
End Function
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Friday, September 17, 2004 1:49 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Connecting to SQL from Access w/a single login?
Hi Christopher:
The SQL server has it's own way of dealing with access rights. They are
through an internal 'sa' type account or it is through 'NT
authentication'.
Traditionally, I only use the 'sa' accounts as a backdoor for an
administrator. Your best option would be to go with 'NT authentication'.
The setup is fairly easy, if network has a domain. Go to the main
server, login as an administrator, 'right-click' MyComputer (assuming a
Windows2000 system), select the 'Manage' option, choose 'Local Users and
Groups' then:
One; create a login group called something like 'sqlusers'.
Two; create or add individual login to the group.
Once all the appropriate users have been added to this group the access
the SQL Enterprise manager. 'EM'.
One; Expand the current SQL server, the 'Security' directory and the
'click'
on the logins icon.
Two; 'right mouse click', select 'New login' and, on the General tab,
from the popdown list select your 'sqlusers' group. Make sure that the
'Authentication' radio button is selected and the current domain/server
is visible.
Three; Go back to the 'Database' and 'MyDatabase' directory and
subdirectory and 'right mouse click' the users icon.
Four; Select 'New database user' and select the new group you have just
added from the popdown menu.
There is some clean up and defining to do but you have basically defined
access rights. Now all you do is attempt a connection at the point where
the user accesses the FE Access DB and if the connection fails, close
and exit the program. (If you need a sample of code that will connect
to your server just request it.)
You will find this way very clean, secure and not intrusive to the
users.
This way is the only way I now ever setup user accounts.
HTH
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christopher
Hawkins
Sent: Friday, September 17, 2004 11:06 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Connecting to SQL from Access w/a single login?
This is driving me nuts. I know I did this a few years ago, but right
now I'm totally stumped.
A client of mine wrote himself an Access app for use around his office.
Eventually the back-end swelled up to 500MB and problems ensued, so they
decided to go to SQL Server. He upsized the database himself and then
called me in to help get the Access front-end working against SQL
Server.
Here's my current issue; I want all the Access clients to use the same
SQL Server login rather than having a unique SQL Server login for each
domain account.
I went ahead and created an AppUser login and gave it the appropriate
permissions to the application database. I have the AppUser login and
password set in the ODBC DSN. But whenever a user tries to connect, an
error message appears stating that his domain login was not recognized
by the SQL Server. It's not even paying attention to the AppUser info.
I'm surfing BOL right now, but haven't yet figured out what I missed.
And I know I'm missing something.
Any suggestions?
Respectfully,
Christopher Hawkins
Software Developer
(559) 687-7591
http://www.christopherhawkins.com
--------------------------------------------------------------------------------------------------------------------
The information in this email may contain confidential information that
is legally privileged. The information is only for the use of the intended
recipient(s) named above. If you are not the intended recipient(s), you
are hereby notified that any disclosure, copying, distribution, or the taking
of any action in regard to the content of this email is strictly prohibited. If
transmission is incorrect, unclear, or incomplete, please notify the sender
immediately. The authorized recipient(s) of this information is/are prohibited
from disclosing this information to any other party and is/are required to
destroy the information after its stated need has been fulfilled.
Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.
This footer also confirms that this email message has been scanned
for the presence of computer viruses.Scanning of this message and
addition of this footer is performed by SurfControl E-mail Filter software
in conjunction with virus detection software.