It was simple enough to get access to a Microsoft  SQL Server from a Web Service when all were running on the same machine. You use Windows Authentication. But when the services are both running locally, with the user running a Windows 10 Universal app that calls the web service, they won’t be logged in an so the web service needs credential for the SQL service.

 

A class of type Book is to stored in a table called Books in database Library.

 

Local Access and Implicit Table Creation

Using Linq to SQL , entities etc.  This requires a data context for the entity, a connection string (Windows authentication) and a Table instantiation:

        public class VenueContext : DataContext
        {
            // Table<T> abstracts database details per table/data type.
            public Table<Book> Books;

            public BookContext(string connection) : base(connection) { }
        }

        public static void Submit(Book book)
        {
            string ConnectionString =
            @"Data Source=Localhost;" +
            "Initial Catalog=Library;" +
            "Integrated Security=SSPI;";
            BookContext db = new Bookontext(ConnectionString);

            Table<Book> tbl = db.GetTable<Book>();
tbl.InsertOnSubmit(book);
db.SubmitChanges();
}

I had in a previous app had the working but I found it wouldn’t store the book as the table didn’t exist. 
There wasn’t an in-your-face entity based CREATE TABLE API command.  I could have gone through the pain of explicitly creating the table using  SQL command, listing all of the fields and data type but my class has about 25 properties. I wanted an implicit way that created it from the entity. Everything else in Linq to SQL is implicitly with respect to the entity class.

  • After a bit of head-scratching I realised that I just needed a command db,CreateDaatabase()  after the BookContext instantiation. The context specifies the table Books to be created for the database. 
  • This needs to be done without the database being created previously.
        public static void Submit(Book book)
        {
            string ConnectionString =
            	@"Data Source=Localhost;" +
            	"Initial Catalog=Library;" +
            	"Integrated Security=SSPI;";
            BookContext db = new BookContext(ConnectionString);
            
//db.DeleteDatabase(); if (!db.DatabaseExists()) db.CreateDatabase(); Table<Book> tbl = db.GetTable<Book>(); tbl.InsertOnSubmit(book); db.SubmitChanges(); }

You only need to create the database (and table implicitly) once on the first submission, hence the if (db.DataBaseExists) clause.
The commented out DeleteDatabase() is there for testing.

 

Remote Access

The Web Service, where the calls to the database are made does not run in the context of the remote user. You need to add a connection string to the Web Service’s web.config file. In the following, the SQL Server account sa is used.  Its probably better to create another server account for security purposes.

<configuration>  
    <connectionStrings>
      <add name="MyDbConn"
           connectionString="Server=localhost; Database=Library; User Id=sa; password= p@ssw0rd”
           providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Ref: https://msdn.microsoft.com/en-us/library/ff648340.aspx (Need a newer version)
localhost
works here as the SQL server and Web Service, whilst remote to the app, are both on the same system.

Also encryption should be used

 

The connection string in Submit then changes to:

            string ConnectionString =
              ConfigurationManager.ConnectionStrings.ToString();

And remote access to Web Service to post data to the database works.

Smile

 

AND IT ALL WORKS WHEN THE APP RUNS ON MY PHONE OVER WiFi! Smile 
(Web Service and SQL Server on Laptop)

 

Also see Web Service authentication issues (previous blog): Kept getting Network Access Required in a Universal App–An hr or 2