Mike & Doris Manning
mikedorism at adelphia.net
Fri Sep 17 14:47:42 CDT 2004
I don't because we use an ADP and 95% of our users have Office Standard/
Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brett Barabash
Sent: Friday, September 17, 2004 3:30 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Connecting to SQL from Access w/a single login?
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.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com