Connect Mobile Service to an existing database

Question

Hello, 

I have a SQL Azure that I'm using in a WP7 Application and I want to use the same data to use in my Win8 Application that I'm building.

The Mobile Services are just great, I don't need to deploy service... just works. The problem it's I cannot create the Mobile Service on a table create by the "dbo" and all my tables was created by the "dbo" user.

How can I skip this? Do I need to recreate all my database or there is any way to use the tables created this way?

thanks in advance
Paulo Aboim Pinto

Answer 1

Hi Paulo,

There are a few things you will need to do to get this working:

1. You need to move your tables to the schema for your mobile service. You can do this using the ALTER SCHEMA command. If your service name is 'myservice1' and you have a Customer table then the command would be:

ALTER SCHEMA myservice1 TRANSFER dbo.Customer

2. Mobile Services won't detect your tables automatically, so you will need to create your tables from within the portal. It will see that the table already exists in the database and use that table (it won't overwrite your data).

3. The primary key column for each of your tables needs to be 

[id] [int] IDENTITY(1,1)NOTNULL
(bigint works too) 

Note that case sensitivity matters. If your PK column is 'ID' then you will run into some issues - for example the browse tab will not work correctly.

Let me know how it goes!

Paul

Answer 2

My primary keys on existing tables are all in the format tablename_id.  This was originally dictated because I have about 10 tables which have many-to-many relationships with each other.  I'm not sure how to differentiate between the tables in the many-to-many without unique names on the ids.  Is there anything I can do to get around this?

Answer 3

Typically I differentiate between the ids for different tables by qualifying the column on table name (select person.id, address.id ...).

Unfortunately we do not have any support for customizing the name of the primary key on tables that Mobile Services works with. The only thing I can suggest at the moment is for you to rename your primary keys columns. Sorry for the inconvenience!

Answer 4

Hello,

It would be a big help to add the information provided here to the instructions of the mobile services.

Had to waste some time to figure these things out by myself. 

I did not have an existing database but i wanted to alter the tables, but i did not find any documentation about the requirement of the 'id' column. Had to find this out via server responses etc.

Answer 5

Paul,

Is this still the only supported way to do BYOD?  Must rename the PKs and transfer table schema name?  If so, will true BYOD be supported in the future.  The idea is to add existing db to Zumo with zero change to existing database.

TIA,

Josh

Answer 6

Hi Josh,

The situation is improved if you are using the .NET backend. You can change the schema it uses by modifying the code in the entity framework context (usually <servicename>Context.cs). Specifically this line:

 string schema = ServiceSettingsDictionary.GetSchemaName();

You can change this to just schema = "dbo", for example. But you'll need to make sure that the SQL user in the connection string has access to dbo.

In regards to IDs, again with the .NET backend there are a few more options. You can map from your model (with a custom schema) to a DTO that has the schema we expect (id column, etc). My coworker Yavor has some sample code that shows this here:

https://github.com/yavorg/samples/tree/master/DotNetRuntimeData

Answer 7

Hi Josh,

The situation is improved if you are using the .NET backend. You can change the schema it uses by modifying the code in the entity framework context (usually <servicename>Context.cs). Specifically this line:

 string schema = ServiceSettingsDictionary.GetSchemaName();

You can change this to just schema = "dbo", for example. But you'll need to make sure that the SQL user in the connection string has access to dbo.

Help me in this situation please. I have a database that I created for cloud services. And all tables are in dbo schema. Now I want to access them from mobile services too. I use "<add key="MS_TableSchema" value="dbo" />" in web.config and get exception: The specified schema name "dbo" either does not exist or you do not have permission to use it.

How can I get to give permission for user?

I found the following article: http://weblogs.asp.net/fredriknormen/database-migration-and-azure-mobile-service-adventure

It offers a solution: GRANT CONTROL ON SCHEMA::[dbo] TO [The Generated User Login]

But I could not understand that it is necessary to specify instead [The Generated User Login](I tried User ID from MS_TableConnectionString. it did not help)

I also tried to use the connectionString from CloudServices, but this gives the same exception.

Thank you in advance for your reply.

Answer 8

Hi Sergey,

The SQL Azure documentation on the topic of managing users is here:

http://msdn.microsoft.com/en-us/library/azure/ee336235.aspx

For example, you can grant access to the dbo schema to your user by issuing the following command:

GRANT CONTROL ON SCHEMA :: dbo TO XXXLoginUser

(where XXXLoginUser is the name username used in your connection string)

If you are looking for a GUI based tool, you might have some success with the AUMC (but please note this is a 3rd party tool and not an official Microsoft product):

https://aumc.codeplex.com/

Answer 9

XXXLoginUser species XXXXXXXXXLogin_<mobileServiceName>?

I get the following response: Cannot find the user 'XXXXXXXXXLogin_<mobileServiceName>', because it does not exist or you do not have permission.

UPDATE: Ok, I understand. Lacked "User". Fully it looks like this XXXXXXXXXLogin_<mobileServiceName>User.

Answer 10

Glad to hear you got it working! I guess the default username format we generate has changed over time and I was using an older format in my example.