Using SQL Server Application Roles with Linked Tables

[Guest]

Hello, I'm trying to have my Access application that has linked tables use
SQL Server Application Role feature. I provided my current code below,
basically it drops all the existing linked tables and then relinks them.

I know that their is a stored procedure called sp_SetAppRole.
Do I have to call this procedure for each table I'm linking?

Thanks


Public Sub DBConnect(sServerName As String, sDBName As String)

Dim sSQL As String
Dim TableName As String
Dim locDB As localDB
Dim rs As Recordset
Dim MyDB As Database
Dim td As TableDef

DoCmd.Hourglass True
SQLConnectSERVER = sServerName
SQLConnectDatabase = sDBName

sServerName = "SERVER=" & sServerName & ";"
sDBName = "DATABASE=" & sDBName & ";"

Set MyDB = DBEngine.Workspaces(0).Databases(0)

sSQL = "Select * From qryDBConnections WHERE Connect Like '*" &
sServerName & "*' AND Connect Like '*" & sDBName & "*'"
Set rs = MyDB.OpenRecordset(sSQL)

If Not rs.EOF Then

SQLCONNECTSTRING = "ODBC" & _
";DRIVER={SQL SERVER}" & _
";SERVER=" & SQLConnectSERVER & _
";AppName=" & gsAppName & _
";DATABASE=" & SQLConnectDatabase & _
";TRUSTED_CONNECTION=Yes;"

SQLParamQryString = "DRIVER={SQL SERVER}" & _
";SERVER=" & SQLConnectSERVER & _
";AppName=" & gsAppName & _
";DATABASE=" & SQLConnectDatabase & _
";TRUSTED_CONNECTION=Yes;"

RelinkAllPassThroughQueries

'Cycle through any and all linked tables and remove connection
sSQL = "Select * From qryDBConnections"
Set rs = MyDB.OpenRecordset(sSQL)

Do Until rs.EOF
Debug.Print "Delete " & rs!TableName
On Error Resume Next
DoCmd.DeleteObject acTable, rs!TableName
On Error GoTo Err_Routine
rs.MoveNext
Loop
''''''''''''''''''''
'Cycle through all tables that should be attached and create linked tables
sSQL = "select * from lstTables ORDER BY TableName"
Set rs = MyDB.OpenRecordset(sSQL)

Do Until rs.EOF
Set td = MyDB.CreateTableDef(rs!TableName)
td.Connect = SQLCONNECTSTRING
td.SourceTableName = rs!TableName
MyDB.TableDefs.Append td
If Not IsNull(rs!UniqueIdentifiers) Then
'debug.print rs!TableName
sSQL = "CREATE UNIQUE INDEX PK_" & rs!TableName & " on " &
rs!TableName & "(" & rs!UniqueIdentifiers & ")"
MyDB.Execute sSQL
End If

rs.MoveNext
Loop


End Sub

 

[Sylvain Lafontaine]

Sorry, but in my opinion you cannot use application roles with linked
tables.

 

[Sylvain Lafontaine]

Application roles are strongly associated with a connection. However, when
Access has etablished a connection for a linked table, it drop thereafter -
returning it to the connection pool -and furthermore, it doesn't necessarily
use the same connection when querying one or more tables as the one it used
to etablish the linked connection. In fact, it will use more than one
connection for performing a query for a databound form; so even is you try
to deactivate the connection pooling, this won't work.

 

[Guest]

Thanks, so is it recommended that you just use SQL authentication for Access?
Otherwise any user with Query Analyzer or Access could see the whole
database if I use Windows Authentication.

Also, if you use SQL Authentication is there any way to see the user
connected when viewing processes in SQL Server.

Thanks Again

Don

 

[Sylvain Lafontaine]

Access with linked tables is just that - Access with linked tables; nothing
else, nothing more.

If you have strong security concerns about your data, then you will have to
look at other solutions.

For exemple, one possibility would be to use unbound forms and make all your
updates via VBA code; however, this will strip much of the easiness of using
Access versus other solutions like .NET or VB6.

I don't understand your point about SQL authentication versus Windows
authentication: if you use SQL authentication, it's fairly easy to find the
login and password used for making the connections.

 

[Don]

I have an Access front-end that I compile into a mde, hiding the database
window,not seeing the table tab, etc. How would you be able to get the
password?
Unless I'm missing something I think the SQL authentication is more secured
when you have a mde file.

I create the new screens with VBA code and stored procedures but have to
many existing bound forms to currently convert them all over.

 

[Sylvain Lafontaine]

You have a very strong confidence in the MDE protection. I'm not myself an
Access' hacker but I don't share your confidence on the level of protection
offered by MDE files.

However, if the level of protection required for your data is not too high
then yes, a MDE file might fill the bill.

BTW, any network snooper might reveal the login and password used with
SQL-Server if you don't use SSL internally.

 

[Brendan Reynolds]

Using Windows authentication does not mean that any user can see the whole
database. Users have only the permissions granted to their account or to a
group of which their account is a member.

You can use views to further restrict the data to which a user has access.
For example ...

In the SQL Server Northwind database, create the following table ...

CREATE TABLE [dbo].[EmployeeUser] (
[EmployeeID] [int] NOT NULL ,
[UserName] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeUser] WITH NOCHECK ADD
CONSTRAINT [PK_EmployeeUser] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[UserName]
) ON [PRIMARY]
GO

Now enter a few rows of data ...

ALTER PROCEDURE dbo.EmployeeUser_Insert
AS
INSERT INTO dbo.EmployeeUser (EmployeeID, UserName)
VALUES (1, N'SomeDomain\SomeUser')
INSERT INTO dbo.EmployeeUser(EmployeeID, UserName)
VALUES (2, N'SomeDomain\SomeUser')
INSERT INTO dbo.EmployeeUser(EmployeeID, UserName)
VALUES (3, N'SomeDomain\SomeUser')

Now create the following view ...

SELECT dbo.Employees.*
FROM dbo.Employees INNER JOIN
dbo.EmployeeUser ON dbo.Employees.EmployeeID =
dbo.EmployeeUser.EmployeeID
WHERE (dbo.EmployeeUser.UserName = SYSTEM_USER)

Grant your users permission on this view, not on the underlying tables, and
they can view only the Employees records that you have authorised them to
view, by adding that EmployeeID and the user's UserName to the EmployeeUser
table.