Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Does anyone know if it is possible to have the SQL Server Migration Assistant for Access read from a password protected Access database? The wizard offers no option to provide a password. I tried to save the migration project in order to manually edit a connection string but it seems to store the project file in a binary format.

So far I have had no luck with the online documentation. http://msdn.microsoft.com/en-us/library/hh302873(v=sql.105).aspx

I can't use SSIS in this scenario as this has to be runnable by clients with no logon access to the SQL instance.

If there are third party products that will do this job I'd gladly take a look.

share|improve this question
add comment

1 Answer

SQL Server Migration Assistant for Access (also referred to simply as "SSMA" for short) is a tool designed specifically for migrating databases from older versions of Microsoft Access to Microsoft SQL Server and Microsoft SQL Azure.

One of the many functions of the tool that has a great deal of benefit to cloud computing uses is its ability to work with various types of password protected sources. SQL Server Migration Assistant for Access allows you to both secure database passwords and import them across various servers as you see fit. The passwords can also be exported as well if the need arises. In the world of cloud computing security, having a password protected source becomes very important. A strong password and encrypted data can be the only thing standing between your sensitive information and having that information compromised by individuals with malicious intent.

SQL Server Migration Assistant for Access can secure passwords in one of two ways. The first method is through the use of clear text, which means that the password itself can be found in the "Server" section of either the server connection file or the script file that you're working with. Passwords can also be encrypted, which means that they are stored in that secured form on the local machine in the ProtectedStorage.ssma file. Encrypted passwords can also be removed from SQL Server Migration Assistant for Access at any time by using the appropriate command line tool.

The tool allows you to export an encrypted database password by storing that information in a file on the local machine. That file is then secured for safe keeping and additional storage. Exporting encrypted passwords will require the reading of both the server ID and the password from the local protected storage source.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.