none
Authentication Problem in Silverlight - Cannot connect to SQL Server

    Question

  • Hi All, At the moment, when I try to register a user using the default Business Template in Silverlight, I am getting an error. Basically I am following a tutorial found on Channel 9 on how to build a business app (Event Manager) with Silverlight. "An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5) An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)". I found a blog where it identifies the problem, and followed every step, however I am still getting the same problems. Anyone has had a problem like mine? I have tried hard to search for a similar problem but cannot identify anything similar. Thanks for your help
    Thursday, May 13, 2010 5:04 AM

Answers

  • DAMMM!!!!!  it just hit me like a brick!

    you need to have 2 different connection strings!

    one for the entity model, the one with the "metadata=res://" stuff thats used only for EF connections and *NOT* for standard sql connections.

    the other one for the membership system - the standard SQL / .NET ado.net connection string.

    note that they each use a different provider.

    thats why one works with the entity model and the other works for membership.

    Monday, May 17, 2010 7:47 AM
  • Ok. Given the connection strings you posted..

    Rolemanager and Membershipprovider should use "SIEventManagerConn" connectionstring.

    "SIEventManagerEntities" should ONLY be used inside your EDMX code-behind file.

    Monday, May 17, 2010 1:41 PM

All replies

  • The error is saying it cannot find the sql server. You should check that the server (data source) in the connection is correct.   If you are using sqlexpress on your machine you should check that the sqlexpress service is running.  Finally I would check that I could connect to the server with the sql managment studio

    Thursday, May 13, 2010 5:18 AM
  • are you running SQL Server in the same machine as your application?

    IF NOT, you need to check the remote connection configuration.

    Go to start->all programs->microsoft sql server 200x->configuration tools->sql server 200x surface area configuration

    select surface area configuration for services and connections.

    locate RemoteConnections.. make sure Local and remote connections is selected. choose both TCP/IP and names pipes.

    this issue is more on sql server connectivity issue.

    Thursday, May 13, 2010 5:18 AM
  • How about you follow this walk through for authentcation service

    http://msdn.microsoft.com/en-us/library/ee942449(VS.91).aspx

    An error has occurred while establishing a connection to the server

    This err shows if your db is in SqlServer and your login id and pass...one of them is wrong. Have you tried using Windows Authentication to connect to the db in Sql Server? If windows authentication throws this error, then your sqlserver service is off.

    here is another article

    http://sharkerkhaleedmahmud.wordpress.com/2009/12/29/using-asp-net-profile-in-silverlight-part-1-create-profile/

    It shows how to connect to aspnetdb in SQL server

    Sharker Khaleed Mahmud

    Thursday, May 13, 2010 5:25 AM
  • Hi Ken The connection string was created automatically by the Domain Service via the WCF RIA. Here it is:- However there seems to be a problem connecting with it, since when I open the ASP.NET Web Application Administration, it cannot even find a provider:- Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.
    Thursday, May 13, 2010 7:35 AM
  • Hi Shihalv, check all the database config and everything seems to be fine
    Thursday, May 13, 2010 7:36 AM
  • Shamrat I check that article which is very useful, however I cannot even get the Provider as yet. Trying to figure out how to do that
    Thursday, May 13, 2010 7:37 AM
  • let's take this one step at a time folks, there were good ideas posted but possibly to many things at one time.

    monfu:

    is the sql database on your local pc?

    is it sql express ?

    the error message you got is a very common one and as was said it has to do with the connection string not working. this can be for a few simple reasons.

    but let's start at the beginning with where it is and how you are getting there. 

    the tools made a string but that's not always perfect. for example if i am running locally but my app is on a different server that other server may not be able to get to the database.

    so give us a better picture of what you are doing when you run the app.

     

    Thursday, May 13, 2010 7:47 AM
  • Hi Figuerres. Ok so the SQL Database is on my local machine yes. An I am using a full SQL Server 2010 and not the SQL Server Express instance. Now the default connection string that the Domain Service gave me is this :- add name="SIEventManagerEntities" connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MONFU-PC;Initial Catalog=SIEventManager;Integrated Security=SSPI;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" That works fine to connect and run the Silverlight website, however I fail to register a user. I tried to tweak it a bit like this:- add name="SIEventManagerEntities" connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MONFU-PC;Initial Catalog=SIEventManager;User Id=johann;Password=mypassword;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" However now, its not even getting the Events in the home page, its telling me that login failed for user 'johann' As an extra step, I went in SQL Server 2010, when i have the user 'johann', went Properties --> User Mapping, and mapped the SIEventManager database, and set all the database roles to ticked, just as a test, but still cannot display the website.
    Thursday, May 13, 2010 8:00 AM
  • Ok,

     

    first thing is that registering a user is not part of the connection you posted.

    user login, registering and roles are part of the asp.net membership system.

    the connection info you posted is for the RIA services to work with an Entity / DOmain model related to your sql database.

    you might want to go to the asp.net web site and start with the basics of how to setup the asp.net membership system and do a small login example from that.

    when you know how that works and can login with asp.net you can then apply that to your RIA project.

    the RIA user auth and roles uses the asp.net as the "foundation"

    so get the base working first then take the step up to the RIA / Silverlight bits.

    Thursday, May 13, 2010 10:15 AM
  • i know how the asp.net works, since i already created some websites. The problem is that the ASP.NET Authentication website cannot even find the provider. And when I try to log in with the user I am using, I cannot even log into the sql server management studio
    Thursday, May 13, 2010 12:15 PM
  • did you mean SQL Server 2008 R2? (sql server 2010)

    Seems like you don't have a valid account for your SQL Server.

    What is the authenticatino mode of your SQL Server?  Did you choose Mixed Mode instead of just Windows Authentication?

    Do you have the password of sa account?

    Thursday, May 13, 2010 12:30 PM
  • sorry sql server 2008 yes. Yes I am using mixed mode. Yes I have the password for sa also. I created a new user Events, and when trying to log in, i get the same error, Login failed for user 'events'.Error 18456
    Thursday, May 13, 2010 1:14 PM
  • so just to clarify that understand correctly.

    you can't login to your sql server locally from management studio with any accounts that you use? is this correct?

    have you tried logging in using the "sa" account?

    Thursday, May 13, 2010 10:12 PM
  • monfu, no offence was meant.

    some folks are here who have never done anything in .net before and are totally new to it.

    some folks started writing membership code cuase they did not know how to use the built in stuff.

    so if you put a login control on the silverlight project with asp.net forms does that work ?

    if it does then the silverlight login should also work.

    if not then something in the asp.net and sql is not setup right.

    Friday, May 14, 2010 8:48 AM
  • Yeah I think its something with my SQL Server 2008. I created a user and tried to login with it but I got a message that I could not. I will go through some tutorials on how to set this up properly. I guess the code is correct since its out of the box
    Friday, May 14, 2010 8:51 AM
  • Hi Monfu, I'm a bit puzzled on how you were able to create a user in the first place when you can't login to the SQL Server 2008?

    did you use "sa" account to login and create a user? if so then the user you created was not properly configured.

    under Security->Logins-> select the user you created .. and in Server Roles check the sysadmin.

    Friday, May 14, 2010 1:30 PM
  • shihalv: i think the OP is able to use sql management studio, just that his application is not connecting.

    at least that's what i get from the posts so far...

    Friday, May 14, 2010 1:54 PM
  • Ok so now I managed to create a new user johann, and put this user as sysadmin, and I am managing to display data in my application with this connectionstring:- However when trying to create a user through the management console in the application I am still getting this error:- An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5) An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326). I followed this excellent tutorial on how to fix the problem:- http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ but to no avail.
    Saturday, May 15, 2010 4:55 AM
  • can you check your web.config if the connection string used by your ASPNetMembershipProvider is the same you're using the in app?

        <membership defaultProvider="AspNetSqlMembershipProvider">
          <providers>
            <clear />
            <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="SIEventManagerEntities" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="FoederiSuite" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="999" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
          </providers>
        </membership>

    Saturday, May 15, 2010 5:17 AM
  • Ok there was no membership added in the tutorial I am doing, however I added it, but still getting the error. I think that the new Silverlight Business Template does not need it. I stand to be corrected though, need to read more
    Saturday, May 15, 2010 5:48 AM
  • Ok so I determined half the problem :) When I put this connectionstring:-

    add name="SIEventManagerEntities" connectionString="Server=(local);Database=SIEventManager;User Id=johann;Password=password;" providerName="System.Data.SqlClient"

    I manage to create a user, however I am not retreiving any data.

    With this connectionstring, which is using the Domain Service and Entity Framework, I cannot create users:-

    add name="SIEventManagerEntities" connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MONFU-PC;Initial Catalog=SIEventManager;User Id=johann;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"
    Saturday, May 15, 2010 6:22 AM
  • Is possible to show us your web.config file?

    Saturday, May 15, 2010 6:35 AM
  • Yes sure why not. How can i post it here? Copy and paste?
    Saturday, May 15, 2010 7:32 AM
  • Of course you can. That's what this forum is for.

    You can click on the source code button on the editor and paste it there to make it more readable.

    Sunday, May 16, 2010 6:33 AM
  • Here it is 

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <sectionGroup name="system.serviceModel">
          <section name="domainServices" type="System.ServiceModel.DomainServices.Hosting.DomainServicesSection, System.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" allowDefinition="MachineToApplication" requirePermission="false" />
        </sectionGroup>
      </configSections>
      <system.web>
        <httpModules>
          <add name="DomainServiceModule" type="System.ServiceModel.DomainServices.Hosting.DomainServiceHttpModule, System.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        </httpModules>
        <compilation debug="true" targetFramework="4.0">
          <assemblies>
            <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
          </assemblies>
        </compilation>
        <membership>
          <providers>
            <clear />
            <add name="AspNetSqlMembershipProvider"
                 type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
                 connectionStringName="SIEventManagerEntities"
                 enablePasswordRetrieval="false"
                 enablePasswordReset="true"
                 requiresQuestionAndAnswer="true"
                 applicationName="/"
                 requiresUniqueEmail="false"
                 passwordFormat="Hashed"
                 maxInvalidPasswordAttempts="5"
                 minRequiredPasswordLength="7"
                 minRequiredNonalphanumericCharacters="1"
                 passwordAttemptWindow="10"
                 passwordStrengthRegularExpression=""/>
          </providers>
        </membership>
        <roleManager enabled="true">
          <providers>
            <clear />
            <add connectionStringName="SIEventManagerEntities" applicationName="/"
              name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
          </providers>
        </roleManager>
        <authentication mode="Forms">
          <forms name=".SIEventManager_ASPXAUTH" />
        </authentication>
        <profile>
          <properties>
            <add name="FriendlyName" />
          </properties>
        </profile>
      </system.web>
      <system.webServer>
        <validation validateIntegratedModeConfiguration="false" />
        <modules runAllManagedModulesForAllRequests="true">
          <add name="DomainServiceModule" preCondition="managedHandler" type="System.ServiceModel.DomainServices.Hosting.DomainServiceHttpModule, System.ServiceModel.DomainServices.Hosting, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
        </modules>
      </system.webServer>
      <system.serviceModel>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />
      </system.serviceModel>
      <connectionStrings>
        <remove name="SIEventManagerEntities"/>
        <!--<add name="SIEventManagerEntities" connectionString="Server=(local);Database=SIEventManager;User Id=johann;Password=password;" providerName="System.Data.SqlClient"/>-->
        <add name="SIEventManagerEntities"
             connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;
             provider connection string="
             Data Source=MONFU-PC;Initial Catalog=SIEventManager;User Id=johann;Password=password;MultipleActiveResultSets=True""
             providerName="System.Data.EntityClient" />
    
        <!--<add name="SIEventManagerEntities" 
             connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MONFU-PC;Initial Catalog=SIEventManager;Integrated Security=SSPI;MultipleActiveResultSets=True"" 
             providerName="System.Data.EntityClient" />-->
      </connectionStrings>
    </configuration>
     
    Sunday, May 16, 2010 6:46 AM
  • I think the web.config looks okay.  Just some things to try below:

    change

    <roleManager enabled="true">

    to

    <roleManager enabled="true" defaultProvider="AspNetSqlRoleProvider" cacheRolesInCookie="true" cookieName=".MyRoles">

    and

    change

    <membership>

    to

    <membership defaultProvider="AspNetSqlMembershipProvider">

    Sunday, May 16, 2010 9:31 AM
  • DAMMM!!!!!  it just hit me like a brick!

    you need to have 2 different connection strings!

    one for the entity model, the one with the "metadata=res://" stuff thats used only for EF connections and *NOT* for standard sql connections.

    the other one for the membership system - the standard SQL / .NET ado.net connection string.

    note that they each use a different provider.

    thats why one works with the entity model and the other works for membership.

    Monday, May 17, 2010 7:47 AM
  • @shihalv = Sorry did not have time to try it yesterday, will try it this evening. @ figuerres = So the normal connection should be named differently, something like EventManagerConnection, and then I have to apply the ASPNETMembership to it right? Will try it this evening
    Monday, May 17, 2010 7:53 AM
  • I think Figuerres could be right! try his idea. I just realized we have 2 sets of connection strings too in our project. great catch!

    Monday, May 17, 2010 7:59 AM
  • Ok seems like I am getting somewhere finally

     Here is the connectionstrings:-

     

     

        <remove name="SIEventManagerConn"/>
        <add name="SIEventManagerConn" connectionString="Server=(local);Database=SIEventManager;User Id=johann;Password=password;" providerName="System.Data.SqlClient"/>
        <!--<add name="SIEventManagerEntities"
             connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;
             provider connection string="
             Data Source=MONFU-PC;Initial Catalog=SIEventManager;User Id=johann;Password=password;MultipleActiveResultSets=True""
             providerName="System.Data.EntityClient" />-->
    
        <add name="SIEventManagerEntities" 
             connectionString="metadata=res://*/EventManagerDBModel.csdl|res://*/EventManagerDBModel.ssdl|res://*/EventManagerDBModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MONFU-PC;Initial Catalog=SIEventManager;Integrated Security=SSPI;MultipleActiveResultSets=True"" 
             providerName="System.Data.EntityClient" />
    
     

    But I still have a problem with the SQL Connectionstring.  I have something missing i guess

    Monday, May 17, 2010 1:02 PM
  • Ok. Given the connection strings you posted..

    Rolemanager and Membershipprovider should use "SIEventManagerConn" connectionstring.

    "SIEventManagerEntities" should ONLY be used inside your EDMX code-behind file.

    Monday, May 17, 2010 1:41 PM
  • Excellent! Thanks a lot guys. Its working now. Phewwww :)
    Monday, May 17, 2010 2:17 PM
  • Glad we got it!

    it was there staring me in the face but i did not see it for a while....

    I know at one point i think i posted some thing about it but i was missing that you only had one or the other untill this morning....

    So how many programmers does it take to change a lightbulb?

    None - It's a HARDWARE problem!

    Lol ....

    Monday, May 17, 2010 2:39 PM
  • Yeah good catch there mate! And I was saying, so 1 connection is working for EMX and the other for membership LOL. Did not come to mind to insert both connections :) To tell you the truth, I still have a minor problem with the code, after inserting a user, but at least, the connection part of the problem (and my biggest problem) is solved now! Thanks for all the brains that helped :)
    Tuesday, May 18, 2010 3:31 AM
  • You're very welcome.  We're all glad it was solved.

    Credit goes to Figuerres for seeing that glitch.

    Tuesday, May 18, 2010 5:14 AM