January 17, 2014 at 11:49 am
My company has several Access database applications.
1 of the databases is setup with user level permissions which requires people to login in order to be able to access that database. Only certain people are allowed to modify records in several of the tables. Everyone else had read-only permission.
There are several other databases that have linked tables pointing to the database that has the user level security.
For the purposes of incorporating the databases into web applications, I’m planning to move the data in the Access database with user level security into a MS SQL database. I will then link the tables in Access to the MS SQL database via an ODBC connection. This way both the users of the Access database and my web application can share the data with SQL Server becoming a central repository.
What I would like to know about is the security component. I know that my web application itself controls who on the web can write to/read from which tables.
When it comes to setting up the security from within Access, do I keep the user level security and have Access control things or do I manage things from within SQL Server? I’m asking because multiple users share the same access database, which means that they will use the same SQL Server login to link data from SQL server into Access. Thus SQL Server won’t be able to discern who is using the Access database.
Will Access treat the tables linked to SQL Server as if they were stand alone tables and apply user level security as it has before the change or do I have to do something in SQL Server to handle the security?
Mike
January 17, 2014 at 1:48 pm
Obviously, if all users look the same in SQL Server, there is not much you can do.
Then again, why would you not be able to set up the linked tables with Windows authentication? In that case, each user is distinct in SQL Server.
Full disclosure: I know nothing about Access.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
January 22, 2014 at 1:27 pm
Access does not have linked tables with windows authentication.
If I am correct, Access will link to SQL server using one SQL Server login where the credentials are stored when the tables are linked. This way all the users of Access will look the same. Access has the ability to generate user level security. Access will then control who gets to update which table. Access does not care where the data resides. It can be in another Access database, a SharePoint List or in an SQL Server Database.
based upon the above, I need to do nothing more than link delete the tables in Access and link them to SQL Server. Right?
January 22, 2014 at 2:28 pm
Looks like it would be great if someone who knows Access can step in. But if all users look the same to SQL Server, there is not much you can do there.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
January 23, 2014 at 8:49 am
Access linked tables can definitely use Windows Authentication, DSN or not. Each table in each Access DB can use its own authentication.
Pass-through queries, too, can use their own authentication.
Here's sample Access VBA code that can create DSN-less connections (i.e. all information stored in Access, no explicit ODBC DSN required), with options for Windows authentication.
Option Compare Database
Function AttachDSNLessTable(stDriver As String, stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, stApp As String, stEncrypt As String, stTrustServerCertificate As String, Optional stUsername As String, Optional stPassword As String)
' How to use, basic:
' Open the Access Database.
' If there's a security warning at the top, be sure this is really the DB you think it is, select it, and "Enable" functions.
' Alt-F11 to open up VBA
' In the upper left, right-click on the DB name and Insert a new module
' Copy and paste this entire function (AttachDSNLessTable) into the window
' Go to the Immediate window (Ctrl-G)
' copy and paste into the Immediate window:
' SQL 2012
' print AttachDSNLessTable("{SQL Server Native Client 11.0}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")
' SQL 2008, 2008 R2
' print AttachDSNLessTable("{SQL Server Native Client 10.0}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")
' SQL 2005
' print AttachDSNLessTable("{SQL Native Client}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")
' update the arguments as required, (driver name, etc.) - see below for details on each one.
' Place the cursor on the print statement for each table you're linking and press Enter, one at a time - "True" means it worked, "False" means it failed.
' It's easiest to start at the top - the cursor will automatically (Access 2007) move through the list
' If you get a "False" response and an "Invalid Argument" error, note that there are undermined maximum lengths, possibly in the range of 270 characters overall.
' After you get all "True" responses, close VBA and close Access.
' When it asks if you want to save the new module, select "NO" - there's no need nor reason to preserve it, and if you
' save VBA you're likely to get more Microsoft security warnings.
'
'
' originally from http://support.microsoft.com/kb/892490
' 20120823 added stDriver, stApp, stEncrypt, stTrustServerCertificate as required parameters
' Encryption DOES NOT ACTUALLY work with the "SQL Server" client stDriver (preliminary indications per Wireshark,
' Win7 x64 to SQL Server 2005)
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stDriver: Name of the ODBC/OLEDB driver to use.
'// "SQL Server" is the old style driver.
'// "{SQL Native Client}" is SQL Server 2005's native client
'// "{SQL Server Native Client 10.0}" is SQL Server 2008 and 2008 R2's native client
'// "{SQL Server Native Client 11.0}" is SQL Server 2012's native client
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stApp: Text of the Application (APP=) string sent to the database server; make this descriptive (like the name of the access database)
'// DBA's will use this to help them identify where your SQL is coming from - "Microsoft Access" is not helpful, but
'// "MSAccess Finance Annual Report RT452" could be quite helpful, especially if it's from "FinanceAnnualReportRT452.mdb".
'// stEncrypt - "Yes" or "No" required. ERRORS out when "Yes" and stDriver = "SQL Server", since initial
'// testing shows encryption MAY NOT ACTUALLY work with the "SQL Server" stDriver (preliminary indications per Wireshark)
'// stTrustServerCertificate - "Yes" or "No" required.
'// If your SQL Server was not set up with a deliberately created certificate, this must be "Yes" if stEncrypt is "yes"
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection (Windows Authentication)
'// stPassword: SQL Server user password, leave blank to use a Trusted Connection (Windows Authentication)
'// Ex (SQL 2008 R2):
'// print AttachDSNLessTable("{SQL Server Native Client 10.0}","AccessTableName","SQLServerTableName","SQLServerInstance","SQLServerDatabase","Application Name","Yes","Yes","Optional SQL Server User Name","Optional SQL Server User Password")
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
If stEncrypt = "Yes" And stDriver = "SQL Server" Then
MsgBox "Use stDriver {SQL Server Native Client 10.0} or {SQL Server Native Client 11.0} if you need encryption - packet sniffing shows this driver may not encrypt"
GoTo AttachDSNLessTable_Err
ElseIf stEncrypt = "Yes" Then
'// Valid option
ElseIf stEncrypt = "No" Then
'// Valid option
Else
MsgBox "Invalid stEncrypt value - enter Yes or No as a string"
GoTo AttachDSNLessTable_Err
End If
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=" & stDriver & ";APP=" & stApp & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes;Encrypt=" & stEncrypt & ";TrustServerCertificate=" & stTrustServerCertificate
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=" & stDriver & ";APP=" & stApp & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword & ";Encrypt=" & stEncrypt & ";TrustServerCertificate=" & stTrustServerCertificate
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
Sub ListODBCTableProps()
' originally from http://accesstips.wordpress.com/2011/05/31/microsoft-access-dsn-less-linked-tables-tabledef-append-or-tabledef-refreshlink/
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left$(tdf.Connect, 5) = "ODBC;" Then
Debug.Print "----------------------------------------"
For Each prp In tdf.Properties
'Skip NameMap (dbLongBinary) and GUID (dbBinary) Properties here
If prp.Name <> "NameMap" And prp.Name <> "GUID" Then
Debug.Print prp.Name & ": " & prp.Value
End If
Next prp
End If
Next tdf
Set tdf = Nothing
Set db = Nothing
End Sub
'Don't forget to change the name of this procedure
Function GetCnnString() As String
' originally from http://accesstips.wordpress.com/2011/05/31/microsoft-access-dsn-less-linked-tables-tabledef-append-or-tabledef-refreshlink/
' WARNING WARNING WARNING the driver here is SQL Server 2008 and 2008 R2; see above for drivers for other versions.
GetCnnString = "ODBC;" _
& "DRIVER={SQL Server Native Client 10.0};" _
& "SERVER=MyServerName;" _
& "UID=MyUserName;" _
& "PWD=MyPassW0rd;" _
& "DATABASE=MySQLDatabaseName;" _
& "Encrypt=Yes"
End Function
Function SetDSNLessTablesNViews() As Boolean
' originally from http://accesstips.wordpress.com/2011/05/31/microsoft-access-dsn-less-linked-tables-tabledef-append-or-tabledef-refreshlink/
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnection As String
SetDSNLessTablesNViews = False 'Default Value
Set db = CurrentDb
'Use a Function to get the Connection string
'Note: In actual use I never use "Connection" in my Variables or Procedure names.
'I disguise them to make it hard for a hacker to use code to get my Connection string
strConnection = GetCnnString()
'Loop through the TableDefs Collection
For Each tdf In db.TableDefs
'Verify the table is an ODBC linked table
If Left$(tdf.Connect, 5) = "ODBC;" Then
'Skip System tables
If Left$(tdf.Name, 1) <> "~" Then
Set tdf = db.TableDefs(tdf.Name)
tdf.Connect = strConnection
If tdf.Attributes < 537001984 Then
tdf.Attributes = dbAttachSavePWD 'dbAttachSavePWD = 131072
End If
tdf.RefreshLink
End If
End If
Next tdf
SetDSNLessTablesNViews = True
Set tdf = Nothing
Set db = Nothing
End Function
January 23, 2014 at 10:44 am
ODBC Connections can be defined to use Integrated Windows Authentication. To simplify adminstration, individual users can then be added to Active Directory group(s) and those groups can be assigned SQL Server Roles. More info here:
http://blogs.msdn.com/b/sqlsecurity/archive/2011/08/25/database-engine-permission-basics.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply