Most secure way of connecting to SQL Server. (1 Viewer)

Zedster

Registered User.
Local time
Today, 12:32
Joined
Jul 2, 2019
Messages
144
I currently run a number of Access databases as a front end that connect to databases on the company SQL server. The connection method I use is windows authentication. All front end databases I distribute are compiled accde files. The IT department who are responsible for the servers have highlighted a potential security risk. If the connection to the server goes down, when the database is opened then Access throws a dialog box explaining it cannot connect to the server and gives out the server name. Armed with the server name the IT department showed how using a third party app called heidi SQL, a user could connect to the server using their windows authentication, obtain all the database names, see all the tables and do what they want with them. The IT department have suggested setting up a SQL server user and then changing from windows authentication to SQL server authentication for all my databases/objects. I am no expert on SQL Server or it's security, but I originally went down the windows authentication route having read many articles some years ago that suggested this was the most safe route.

Some questions I have:
  • Would changing from windows authentication to SQL server authentication be the best way forward?
  • Is there any way to suppress MS Access displaying the server name upon opening if the network is down? (I figure if they do not have the SQL server name then it becomes a lot harder to use third part SQL editors)
  • Is there any way to remove visibility of databases and/or table names if someone connects to the SQL server using apps like Heidi SQL? (I figure if they can't see database and table names it is a lot harder for them to do damage)
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
9,248
You can check if you can connect to a table via ODBC and if not simply shut down the app, after explaining there is a connection problem.
Code:
Function IsODBCConnected(TableName As String) As Boolean
    
    If Not TableExists(TableName) Then Exit Function
    
    Dim rst As DAO.Recordset
    
    On Error Resume Next
    'Debug.Print Err.Number
    Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
    IsODBCConnected = (Err.Number <> 3151)
    
End Function

If your users are authenticated on your domain, go back to IT and simply open the ODBC connection wizard in windows - it will list any saved ODBC connections. If there aren't any just set up a new one then browse for the server in the dialogue box.
1628172736580.png

This isn't really an Access problem, it is a "not trusting your users" problem.
 

isladogs

CID VIP
Local time
Today, 12:32
Joined
Jan 14, 2017
Messages
16,088
I've not heard of that particular app and would certainly be interested in knowing more about this issue.

Presumably you get a standard Access error message with error number and description. If so, you can add error handling to replace that over informative message with your own which doesn't show the connection info.

With all my ACCESS FEs that are linked to SS BEs, code is run at startup which checks if SS is running. If not, a message is displayed and the FE quits. If that is any help to you, I'll dig out the code.

I'm not an expert user of SS , but it is my understanding that Windows authentication is far more secure.
Do you use ODBC or DSN-less connections?
 

Zedster

Registered User.
Local time
Today, 12:32
Joined
Jul 2, 2019
Messages
144
I've not heard of that particular app and would certainly be interested in knowing more about this issue.

Presumably you get a standard Access error message with error number and description. If so, you can add error handling to replace that over informative message with your own which doesn't show the connection info.

With all my ACCESS FEs that are linked to SS BEs, code is run at startup which checks if SS is running. If not, a message is displayed and the FE quits. If that is any help to you, I'll dig out the code.

I'm not an expert user of SS , but it is my understanding that Windows authentication is far more secure.
Do you use ODBC or DSN-less connections?

I use DSN Less.

I have error handling in all my forms including a splashpage that opens when the database opened (to check username, initialise all variables etc.). I will need to go and experiment I think the Ms Dialog box beats my dialog box. When it pops up it prompts the user to enter server credentials, it displays the server name at that point. Would appreciate if you can dig out the code so I can do some playing along with minty's suggestion.
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
9,248
In your splash screen code make the ODBC connected function check the very first thing that happens.
If you can't connect to your tables then the rest of your app doesn't work anyway, so politely quit.
 

isladogs

CID VIP
Local time
Today, 12:32
Joined
Jan 14, 2017
Messages
16,088
I asked about DSN-less as the way I do this there are no ODBC connections listed.

Here are 2 functions that I use at startup:

Rich (BB code):
Function IsSQLServerInstalled() As Boolean

On Error GoTo Err_Handler

'checks registry to see if SQL Server is installed on this computer

    IsSQLServerInstalled = False

    strText = GetStringValFromRegistry(HKEY_LOCAL_MACHINE, "SOFTWARE\MICROSOFT\Microsoft SQL Server", "IsListenerActive")
    'Debug.Print strText
    If strText <> "" Then IsSQLServerInstalled = True
    
    'Debug.Print IsSQLServerInstalled
    
Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & " in  IsSQLServerInstalled procedure : " & Err.Description
    Resume Exit_Handler
    
End Function

Rich (BB code):
Public Function CheckSQLServerConnection() As Boolean

'returns true if SQL Server is running and the listed database is available
'Otherwise false

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
CheckSQLServerConnection = False

    Set cnn = New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DBName;User ID=DBUser;Password=DBPassword"    

    If cnn.State = adStateOpen Then
        CheckSQLServerConnection = True
        cnn.Close
    End If

    Debug.Print CheckSQLServerConnection   

Exit_Handler:
    Exit Function
  
Err_Handler:
    'CheckSQLServerConnection = False
    'err = -2147467259 'can't open SQL database - server or database name incorrect or SQLServer not running
    'err = -2147217843 'incorrect UserID / password

    If Err = -2147467259 Or Err = -2147217843 Then
        DoCmd.Close acForm, "frmLogoutTimer" 'prevents err = 3146 in hidden form frmLogoutTimer running in the background
    Else
        MsgBox "Error " & Err.Number & " in CheckSQLServerConnection procedure : " & vbNewLine & _
            "  " & Err.Description & "   ", vbCritical, "SQL Server error"
    End If
    
    Resume Exit_Handler

End Function

The error handling kicks in before any standard Access messages can occur

I use these functions both in my startup form and when relinking BE tables with code like this:

Rich (BB code):
 If IsSQLServerInstalled = False Or CheckSQLServerConnection = False Then
    'various code lines specific to my database to run tidy up code before closing
    Application.Quit
End If

Hope that helps
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:32
Joined
Feb 19, 2002
Messages
35,936
In addition to what has already been discussed.

I sometimes build apps for technically sophisticated users. The problem this leads to is they know their userID and they know their password so there is nothing to stop them from creating a new Access database and using the credentials that they already know to link to the database. And because they normally have update access to the data, they can update it if they are malicious or careless.

To get around this, one DBA and I came up with a solution. We would use the user's normal Windows userID but we would generate his password. I can't tell you the technique we used but it involved using data that both the Server and the local PC could get to. So the DBA generated the password when he created their account and I generated their password and used it to log in to the Server when the user opened the db.
 

isladogs

CID VIP
Local time
Today, 12:32
Joined
Jan 14, 2017
Messages
16,088
@Pat Hartman
To clarify, I believe you are referring to using SQL Server authentication.
That isn't relevant to Windows authentication as users NEVER enter the userID/password themselves.
If they are unable to view the connection string info, they cannot use this to login to the server from a different Access app.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:32
Joined
Feb 19, 2002
Messages
35,936
Yes, SQL authentication. there has to be a password assigned and in the beginning I had the user log into Access with the userID and password and then we discovered that one of the engineers had created an Access app and used those credentials to link to the database. That is what prompted the change.

Too many Access apps use the developer's credentials for everything. This makes analysis impossible for the DBA but it does save him the work of managing user access so they tend to go along with it.

I'm not sure if Windows authentication prevents the users from freelancing with their own Access apps. I don't have a server I can use to confirm/deny.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Jan 20, 2009
Messages
12,462
Is there any way to remove visibility of databases and/or table names if someone connects to the SQL server using apps like Heidi SQL? (I figure if they can't see database and table names it is a lot harder for them to do damage)

Huge amount of work to implement but it is the ultimate security solution. Do everything by sending commands to execute parameterised Stored Procedures on the server and only give users server permissions to Execute them. Parameterised Stored Procedures are not subject to injection vulnerabilities. The user can't see anything on the server database.

A Stored Procedure can return a recordset which can be set as the Recordset property of an Access form. However it won't be updateable. The work around is to return an ADODB recordset and disconnect it. This allows bound controls to be updateable on the form.

However the recordset cannot be reconnected to the server. Updates and Inserts need to be managed by running more stored procedures. These can be triggered to update the record as the user moves through the form, making it work like a normal Access bound form. Alternatively it can be done for the whole form by looping through the recordset when they click a Save button or go to close the form.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Jan 20, 2009
Messages
12,462
I'm not sure if Windows authentication prevents the users from freelancing with their own Access apps. I don't have a server I can use to confirm/deny.
Nothing to stop them. The server will allow them to connect to whatever they have been granted permission on.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:32
Joined
Feb 19, 2002
Messages
35,936
Nothing to stop them. The server will allow them to connect to whatever they have been granted permission on.
That is why we NEVER gave them their password. We calculated it for them when we needed to. They could use my app but not build their own.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Feb 19, 2013
Messages
14,222
These can be triggered to update the record as the user moves through the form, making it work like a normal Access bound form. Alternatively it can be done for the whole form by looping through the recordset when they click a Save button or go to close the form.
Agree - but the problem is then with record locking - if a user downloads records and disconnects then goes to lunch, another user can come along download, make changes and save. The the first user comes back, makes their changes and saves, overwriting the second users changes.

So the stored procedure needs two sets of parameters - the original values and the new ones. It first needs to compare the original values with those currently on the server and if the same carry on. If not it needs a process to resolve the differences which might simply be a message to say something like 'updates aborted due to other changes, please reload data and resubmit'. Rather than comparing each field, hash all the values into a hash field and compare on that. More space on the server but easier and technically faster to manage.
 

Sun_Force

Active member
Local time
Today, 20:32
Joined
Aug 29, 2020
Messages
396
So the stored procedure needs two sets of parameters - the original values and the new ones. It first needs to compare the original values with those currently on the server and if the same carry on. If not it needs a process to resolve the differences which might simply be a message to say something like 'updates aborted due to other changes, please reload data and resubmit'. Rather than comparing each field, hash all the values into a hash field and compare on that. More space on the server but easier and technically faster to manage.

Is this method actually practical? Because in a multi user environment, as the number of the users grows up. the possibility of what you described multiplies.
It would be frustrating for each user to download a set of data over and over and can not update the server because another user was working on the same set of data too and was faster to send back the update.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Feb 19, 2013
Messages
14,222
I’ve used it - with a disconnected recordset you loop through the records and call the stored procedures for each record where it has changed. You really need to base your forms on very limited number or records
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Jan 20, 2009
Messages
12,462
Is this method actually practical? Because in a multi user environment, as the number of the users grows up. the possibility of what you described multiplies.
It would be frustrating for each user to download a set of data over and over and can not update the server because another user was working on the same set of data too and was faster to send back the update.
That is exactly what happens in bound forms too. It is just that Access takes care of the locking. It is inevitable where two users are working on the same records. Provided the value coming back from the edit is the same as the current value in the table there isn't a problem. But the code must be able to detect that two users are trying to change the same record.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Feb 19, 2013
Messages
14,222
Users creating a record with associated records such as an invoice will need to save the primary record, retrieve the pk (easily done with a stored procedure) which can then be applied to the fk field of the associated records
 

Users who are viewing this thread

Top Bottom