data security (1 Viewer)

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
Hi Guys

How can we ensure data security in sql server? If we have ms access frontend and sql server as a backend then there are chances that any access frontend can access to the data stored in sql server . Is there anything like locking tables / password protection or something similar that will prevent the frontends to access the backend if they don't have access to it? Only the Access frontend which has authoroization can access the backend.

Any help will be much appreciated.

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:37
Joined
May 7, 2009
Messages
19,175
compile you db ad accde. Make sure to disable shift key.
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
I am talking about accessing data from sql server . how to secure data in sql server so that it is accessible by only that frontend which has rights to do that.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:37
Joined
May 7, 2009
Messages
19,175
If they dont know the password to the server then they cannot access the data. Besides you can create a group in your db and assign it the appropriate right to each db object. Only this group have access to the object. On the fe link to this group.
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
how do we password protect sql server database and how can frontend be linked using password? thanks
 

Minty

AWF VIP
Local time
Today, 11:37
Joined
Jul 26, 2013
Messages
10,355
If you have created a ODBC link on a domain workstation , and have used inbuilt Authentication , then the only thing that can connect is a domain authenticated user connected to your network. This level of security is normally more than sufficient.

If that isn't secure enough, then as Arne stated you can lock the front down end, but if it need to get to the data, it needs to get to the data...

Your SQL data is already password protected.
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
There are number of steps that we need to follow in order to link access frontend to sql server backend. In one of the steps you need to choose authentication method for the question “How should sql server verify the authenticity of the login id”. By default 1st option is selected. Will it make any improvement in database security if we choose option 2 while linking??

1. With windows NT authentication using network login.
2. With sql server authentication using a login and password entered by the user.



If you have created a ODBC link on a domain workstation , and have used inbuilt Authentication , then the only thing that can connect is a domain authenticated user connected to your network. This level of security is normally more than sufficient.

If that isn't secure enough, then as Arne stated you can lock the front down end, but if it need to get to the data, it needs to get to the data...

Your SQL data is already password protected.
 

Minty

AWF VIP
Local time
Today, 11:37
Joined
Jul 26, 2013
Messages
10,355
1. is by far the easiest to maintain and keep secure. Generally people that leave will have their domain account disabled, instantly denying them access.

Also group policy will usually determine that password strength and regular password changes are enforced, all without you need to do anything in the background with your app.

If you use a SQL server account , you would have to maintain all these functions yourself.
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
The one problem my boss was talking about is anyone who know the IP address and ODBC connection string to that server can access that backend. how can we avoid that to happen?
 

Minty

AWF VIP
Local time
Today, 11:37
Joined
Jul 26, 2013
Messages
10,355
How would they know the correct user name and password to use in the connection string ?
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
when do we pass the username and password in the connection string??? AT what stage?
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
I will be using upsizing wizard in ms access to connect to sql server. So when do we pass the username and password in the connection string without using VBA for linking tables?
 

Minty

AWF VIP
Local time
Today, 11:37
Joined
Jul 26, 2013
Messages
10,355
If you are only using the database within your network, then I would simply set up a DSN on each PC that will be using the database. Your IT Guys can automate this. No connection string required.

If you want to use DSN-Less connections you can link the tables before distributing the FE using trusted connections, and no password information will be saved. Either way you can't see the connection password.
 

Derek

Registered User.
Local time
Today, 04:37
Joined
May 4, 2010
Messages
234
Arnelgp, do you mean creating user groups ?

If they dont know the password to the server then they cannot access the data. Besides you can create a group in your db and assign it the appropriate right to each db object. Only this group have access to the object. On the fe link to this group.
 

Users who are viewing this thread

Top Bottom