Connecting Access database to SQL Azure with limited privileges
-
I am doing some experimenting with using SQL Azure as the data store for a Access 2010 Application. I have created a test database in Access, migrated it to SQL Azure using Microsoft's Server Migration Assistant, and can connect to the migrated Tables both using the links created by the Migration Assistant and using ODBC connections which I have created manually, using the master credentials for the Azure Server. I have created a new login on the Azure server, associated it with a user name for the test database, and given that user read and write privileges on that database. However, when I try to create an ODBC connection with the new login information, the Create New DataSource wizard fails when I 'Finish', saying that "Access to selected database has been denied". Does the limited login/user need some additional privileges, either on the SQL master database or on the test database, or am I missing something else? Almost all the material on the web seems to assume that the developer wants to use the master credentials in this situation (which does, indeed, work), but I don't!