Feb 13

Use Microsoft SQL Server Database Users Rather than Server Logins

FMS President Luke Chung wrote a new paper on improving Microsoft SQL Server security by using database users rather than server logins:

Microsoft SQL Server Database Users and Permissions Instead of Server Logins.

Here’s an overview:

Microsoft SQL Server Security Logins Introduction

To connect to a Microsoft SQL Server database, it requires a login which includes a user name and password. Each database server has a login with administrator permissions that allows the creation and deletion of databases.

Often, this login is distributed which creates a security problem. Developers and end-users shouldn’t have administrator permissions to perform their tasks. Their permissions should be managed for each database.

Database User Permissions

Microsoft SQL Server makes it easy to create and manage database permissions. The permissions you grant are called roles and users are able to have multiple roles. Here are some examples of the permissions you are able to assign to users.

Full Database Permissions (Owner)

This allows the database developer full rights to make modifications to the database objects.

Editing Permissions (Writer)

End-users who need to add, delete and change data in user tables.

Read Only Permissions (Reader)

Only allows the end-user to view data in the user tables.

Read our Microsoft SQL Server Users and Permissions paper for more information on how to do this and some pitfalls to avoid. It applies to SQL Server whether it’s installed on premise or in the Azure cloud.

Dec 29

Microsoft Azure Security Holes with SQL Server Databases

FMS President Luke Chung wrote a new paper to deal with Microsoft Azure Security Holes with SQL Server Databases

Here’s an overview:

Microsoft SQL Azure Security Introduction

Having Microsoft Azure host SQL Server databases on their servers is very cost-effective and efficient. Within minutes, one can have a SQL Server database hosted in the cloud and available to applications on the cloud or on premise.

As with all cloud resources, and especially databases, security is a huge concern. Fortunately, SQL Azure includes features to restrict what can connect to your database server but you need to know how to use them and realize that the default settings do not protect you best.

Setting Firewalls and Virtual Networks

This is an important feature for cloud solutions so that only permitted sources are allowed to get data from your server and databases. You can set the IP Addresses you allow at the database level and server level. The database settings take precedence over the server settings.

Cannot Open Server Error

If you try to connect to the database from an unauthorized IP address, it triggers an error like this:

Cannot open server ‘ServerName’ requested by the login. Client with IP address ‘111.222.33.44’. is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

By Default, All Azure Resources can Connect to Your Database

By default, all Azure resources can connect to your server and databases hosted on Azure:

Allowing All Azure Services to Connect to Your Server is a Huge Security Hole!

If you “Allow access to Azure Services” set to On, you create a huge security hole for your server and every database in it. Not only can all your resources connect to your databases, Any Azure resource from any organization can connect to your database.

This setting is NOT restricted to the Azure resources in your subscription. It’d be nice to restrict permissions to the current subscription or list of subscriptions but that’s not possible. It’s everything on all of Microsoft Azure or you need to specify each IP address.

Turn Off Permissions to All Azure Services

Set the permissions to OFF to disallow all Azure services to connect to your SQL server:

Explicitly Specify the IP Addresses Allowed

To avoid the ability of rogue Azure resources from breaching your database security, you need to manually specify the IP Address of every resource that may connect to your server and databases. This can be a real pain.

For more details and how to set your IP Addresses and SQL Azure security correctly, read our new paper: Microsoft Azure Security Holes with SQL Server Databases

Dec 20

Total Access Analyzer 2019 is Shipping

real-time-monitortotal-access-analyzeranalyzer-icon

We are excited to announce the release of Total Access Analyzer for Microsoft Access 2019! Total Access Analyzer examines all your database objects to provide extensive documentation, code analysis, object cross-reference, and diagrams with over 390 presentation-quality reports.It detects 300+ types of errors, suggestions, and performance tips, so you can learn and apply Best Practices to fix problems, improve your design, and speed up your Access applications.>New Features

Access Analyzer 2019 is an upgrade from the 2016 version and includes these enhancements:

  • analyzerSupports Microsoft Access 2019, 32-bit and 64-bit versions.
  • Document All Database types supported by Microsoft Access 2019.
  • Improved Blueprint Documentation.
  • Additional Cross-Reference and Validation including Subform References.
  • Improved Memory Management.
  • Data Macro Documentation.
  • Document Workgroup Security in ACCDBs.
  • Better Support of Documentation for Multiple Databases.
  • Module Bracket Reports.
  • Improved User Interface Shows more Progress Details.
  • New Manual and Context Sensitive Help.
  • and more

Existing Total Access Analyzer owners are able to upgrade at a discounted price.


New updates for Total Access Analyzer 2016, 2013, 2010 and 2007

Enhancements

  • Additional Cross-Reference and validation of tables, queries, fields and controls across your database objects, including references in subforms.
  • Improved form and report blueprint documentation to capture large and complicated designs and layouts.
  • Improved memory management for documenting large Access databases to minimize the chance of running out of memory.
  • Data macro documentation and cross references to related tables.
  • Documentation of workgroup security settings for ACCDBs if they wer converted from MDBs with workgroup security.
  • Improved user interface to show progress of the documentation.
  • Revised user manual and help file.
  • and more…

For more information, visit:

Download the Free Trial to experience it for yourself.

Oct 09

Microsoft Access has detected that this database is in an inconsistent state

Recently, Microsoft Access users are confronted with this error when they open their database on Windows 10 machines:

“Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open the new database. The names of objects that were not successfully recovered will be logged in the ‘Recovery Errors’ table.”

‘inconsistent state’, Error 3343 “unrecognized database format

This seems to be related to Microsoft security updates that were released over the past few months.Our investigations lead us to these two links:

Microsoft Explanation

Microsoft discusses this problem here: Microsoft Access reports that databases are in an ‘inconsistent state’

Workaround

A potential workaround suggests to ensure that SMBv2 or SMBv3 is enabled on both client and server, as described in this Knowledge Base article:
How to detect, enable and disable SMBv1, SMBv2, and SMBv3 in Windows and Windows Server

We hope you find this helpful. Let us know your experience with this.

Aug 27

Total Access Emailer Update for Microsoft Access 2016

Total Access Emailer is the most popular email automation system for Microsoft Access. A new update was released for the Microsoft Access 2016 version.

Enhancements

  • Partially Restored Original SMTP ValidationTechnique.
  • Validation of FROM Address during Email Blasts.
  • Adjusted the saving of datasheet column settings on databases before Access 2007.
  • Improved Offline activation and uninstall.
  • Cosmetic changes to the program and sample databases.

For more information, visit:

Existing Total Access Emailer 2016 owners were notified to download the update.