ADP Security & SQL Authentication 1

[SQLBI]

Hi,

I'm new to adp's and was wondering if anyone could explain how the adp authenticates with SQL.

Does it use an application specific login, i.e. a single login, regardless of the user or does the adp pass the users Windows credentials to SQL for it to authenticate the user?

Sorry if this is a dumb question, thanks in advance.



Cheers,
Leigh

You're only as good as your last backup!

 

[cmmrfrds]

Whether to use NT or SQL Server Authentication is done at the time of the connection. You can use either.

 

[smedvid]

Or, you can use both... I open the application with NT Authentication and then switch to an Application ID. The users have no permissions to database objects directly... thereby, all database objects can only be updated via the Application ID. htwh,

Code excerpt below...
from Autoexec Module...
'Check NT Authority
DBConnResult = False
DBConnResult = ChangeADPConnection("Server", "dBName")
If DBConnResult = False Then
'User is not autorized to backend SQL Server
MsgBox(lcMsg, lcStyle1, lcTitle)
Application.CurrentProject.CloseConnection
Application.Quit acQuitSaveAll
End If

'Set Application Id
DBConnResult = False
DBConnResult = ChangeADPConnection("Server", "DbName", lcLoginId, lcPassword)
If DBConnResult = False Then
msgbox('''Error;;; ...




Function ChangeADPConnection(strServerName As String, strDBName As _
String, Optional strUN As String, Optional strPW As String) As Boolean

Dim strConnect As String
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
strConnect = strConnect & ";user id=" & strUN
If strPW <> "" Then
strConnect = strConnect & ";password=" & strPW
End If
Else 'Try to use integrated security if no username is supplied.
strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function

EH:
'MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False

End Function


Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SQLBI]

Thanks Steve, Seems like the best way to go bout it.





Cheers,
Leigh

You're only as good as your last backup!

 

[giovi2002]

Hey guys, How can I secure my ade from importing??
When I store my password with SQL-server authentication within the ade the user will simply be able to import from my database by making a new db and use the import function.

Isn't there a property within access to disable the import function like you have a piece likewise the code to disable the shift key


Hail to the king!

 

[teknet]

If you authenticate with NT and then switch to a single application login how would you deal with logging user actions?

I like your single login idea but I am currently using suser_sname to log which user performed certain key operations.

And do you implement any kind of front end security in your ADP? I need to restrict certain forms (which contain code to process transactions with an external payment gateway) to certain users and so SQL security can't help me there.

Thanks,
Steve.

 

[giovi2002]

If you want to prevent updating


Read chapter 14.9 from access cookbook 2002 about using updatable views

1)create views
2) set them to updatable
3) in case you use proc's use the updatable views

don't forget to remove all public authentication on the tables and setting authentication on the views

 

[giovi2002]

NB:
when using stored proc's:Use the updatable views in the from clause, in this way you can still use proc's