Planning and Managing Security - MS Access

Planning and Managing Security

SQL Server offers two security models: Windows authentication, in which a user’s Windows logon is integrated to the SQL Server, and SQL Logins, in which you set up specific accounts with passwords. As of this writing, Azure only supports the use of a SQL Login. One of the most useful functions in SQL Server is SUSER_SNAME(). This function will pick up the identity of either the Windows authenticated user or the name of the SQL Login. Because this function is supported in SQL Azure, you have the option to record information on the individual users accessing your database.

Security can be viewed from either the perspective of preventing unauthorized access to a system or in recording the activities of authorized access. The first aspect of security, which we described earlier in this chapter, was in setting the firewall rules for IP address access to the SQL Azure database. Recall that you can either open up a wide range of IP addresses to allow for access from dynamic IP addresses, or you could use fixed IP address ranges for situations in which you have a range of static IP addresses. As an Access developer, you might already have a security scheme in your application; thus, the issue we discuss here is how to integrate this security scheme with SQL Azure. You have several alternatives for managing security, including:

  • Prompt the user to log on with the Azure logon name and password.
  • Hide the Azure logon name but force the user to enter the Azure password.
  • Hide both the Azure logon and password and map everything through to your own local table of logons and passwords.
  • Hide everything and use a single sign-on for all users.
  • Embed the passwords and user names in the linked tables and forget about security.

Assuming that you have not chosen to simply embed a password and user name in your linked tables, the first time a linked table is opened (interactively or in code), you will be prompted to provide logon credentials. These credentials will remain in force while accessing all the other linked tables to that data source. To avoid the problem of a pop-up box appearing that requests the user to log on, you need to use some program code to open a connection with SQL Azure.

Start with your tables linked to SQL Azure. Note that we have not saved any credentials in the connection string. In this example, you have the following connection string:

In the master SQL Azure database, run the following T-SQL to create an account for testing (CreateUser.sql). The CREATE LOGIN command must be run in the master database, and the command to CREATE USER and assign security should be executed in the NorthwindAzure database:

Now use the following code to make the connection:

This constructs the following connection string:

The previous code can then be easily adapted to integrate with your existing user security model.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics