Connecting to SQL Server

Dec 1, 2011 at 1:52 PM

I downloaded "CodeFirstExample.zip" (2 139 396 bytes) and tried to run it. I want to use a different server than ".\SQLSERVER" because I don't want to install it onto the dev PC. But I am unable to set the data source to the intranet SQL Server which is already up and running just fine. I can manage it from from Visual Studio Server Explorer or from an MVC3 project (without NakedObjects).

 

"SQLEXPRESS" string can be found twice in the solution:

RunMVC\App_Start\RunWeb.cs(45): in EntityPersistorInstaller.EntityCodeFirstConfig

RunMVC\Web.config(55): in ApplicationServices

 

The project in it's original state it says the following when I try to run it: 

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

This is OK, since the SQL Server is not on the current PC.

 

Next I replaced "." to the IP address of the SQL Server in both places:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

This is OK, since the SQL Server requires authentication and I have to provide user ID and password (because the SQL Server runs on a different PC and we don't use ActiveDirectory).

 

Next I provided the user ID and password in both places but no change:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

 

What am I doing wrong?

 

Original:

new EntityPersistorInstaller.EntityCodeFirstConfig {AssemblyName = "CodeFirstExample", DataSource = @".\SqlExpress", DatabaseName = "database1"}}

and

<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

 

Edited:

new EntityPersistorInstaller.EntityCodeFirstConfig {AssemblyName = "CodeFirstExample", DataSource = @"192.168.100.58\SqlExpress;User ID=***;Password=###", DatabaseName = "database1"}}

and

<add name="ApplicationServices" connectionString="data source=192.168.100.58\SQLEXPRESS;User ID=***;Password=###;" providerName="System.Data.SqlClient" />

 

So there's nothing extra, I didn't even modify the code excepting the DB config part.

Thanks

David

Coordinator
Dec 1, 2011 at 2:29 PM

Thanks for reporting this: it is surprising that neither we nor anyone else has encountered this before.  Right now, it seems, you can work with a remote SqlServer (or Express) server, using either Windows- or SQLServer.managed security, in ModelFirst or DatabaseFirst mode.  The CodeFirst has been inadvertently hard coded for Windows-managed security.

I will raise a ticket for us to fix this for the next release and make the security mechanism configurable via the EntityCodeFirstConfig() method.

Meantime, you have two options if you want to work with a remote server and SqlServer managed security

1) Work ModelFirst or DatabaseFirst

2) Make a very small change to the source code and re-build the framework (instructions for building are contained in the sourcecode download  -  it's not difficult).  You need to locate the NakedObjectsContextBuilder class in the NakedObjects.Persistor.Entity project.  Near the top of the class you'll quickly spot where the connection string is being built for CodeFirst operation and you'll see where we've inadvertently hard-coded it for Windows security.  You can temporarily hard code it the other way. 

Dec 1, 2011 at 2:39 PM

OK, good to hear that you will fix it.

One more question: why do I have to specify my SQL connection details twice? Once in web.config and once in EntityCodeFirstConfig? Are they for the same purpose or are they for different ones?

Coordinator
Dec 5, 2011 at 8:21 AM

If you're using code first the connection string for the domain model is built using the details in the code first config. If you're using the standard authentication you'll also need a connection string for the authentication database which is as the normal MVC project templates.