none
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

    Thursday, September 06, 2012 5:56 PM

Answers

  • 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

    Thursday, September 06, 2012 10:23 PM
    Moderator

All replies

  • 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

    Thursday, September 06, 2012 10:23 PM
    Moderator
  • 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?
    Thursday, October 11, 2012 3:43 PM
  • 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!

    Thursday, October 11, 2012 11:52 PM
    Moderator
  • 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.

    Monday, October 22, 2012 6:51 AM
  • 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

    Tuesday, November 18, 2014 1:48 PM
  • 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

    Tuesday, November 18, 2014 5:04 PM
    Moderator
  • 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.


    Thursday, December 04, 2014 12:53 AM
  • 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/

    Thursday, December 04, 2014 1:10 AM
    Moderator
  • 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.

    Thursday, December 04, 2014 1:17 AM
  • 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.
    Thursday, December 04, 2014 2:27 AM
    Moderator