New blog over mssql - cannot open db

Topics: ASP.NET 2.0
Dec 21, 2010 at 6:42 PM
Edited Dec 21, 2010 at 6:56 PM

Config:

Web host - Network Solutions small business model over windows on box 1, sql support running on box 2.

Built a dsn entry on box 1 called dbblogengine which points to the db on box 2.  Populated db on box 2 by running the sql script supplied while logged onto the db via server on local network running SQL 2005 Management Studio.  Established that the user id created for the db has neccessary credentials to read and write entries into the db.  Uploaded v 1.6 of BE.N to web host and modified the web.config code as follows:

<configSections>
        <sectionGroup name="BlogEngine">
            <section name="blogProvider" requirePermission="false" type="BlogEngine.Core.Providers.BlogProviderSection, BlogEngine.Core" allowDefinition="MachineToApplication" restartOnExternalChanges="true"/>
        </sectionGroup>
    </configSections>
    <BlogEngine>
        <blogProvider defaultProvider="DbBlogProvider">
            <providers>
                <add name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>
                <add name="DbBlogProvider" type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core" connectionStringName="BlogEngine"/>
            </providers>
        </blogProvider>
    </BlogEngine>
    <connectionStrings>
        <clear/>
        <add name="BlogEngine" connectionString="Data Source=mydsnname;User ID=myuserid;Password=myuseridpassword;persist security info=False;initial catalog=BlogEngine;" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Results in:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Modify the web.config to this:

<configSections>
        <sectionGroup name="BlogEngine">
            <section name="blogProvider" requirePermission="false" type="BlogEngine.Core.Providers.BlogProviderSection, BlogEngine.Core" allowDefinition="MachineToApplication" restartOnExternalChanges="true"/>
        </sectionGroup>
    </configSections>
    <BlogEngine>
        <blogProvider defaultProvider="DbBlogProvider">
            <providers>
                <add name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>
                <add name="DbBlogProvider" type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core" connectionStringName="BlogEngine"/>
            </providers>
        </blogProvider>
    </BlogEngine>
    <connectionStrings>
        <clear/>
        <add name="BlogEngine" connectionString="server=xxx.xxx.xxx.xxx;User ID=myuserid;Password=mypassword;persist security info=False;initial catalog=blogengine;" providerName="System.Data.SqlClient"/>
    </connectionStrings>

results in:

Cannot open database "blogengine" requested by the login. The login failed.
Login failed for user 'myuserid'.

Suggestions?

Thanks...

 

Coordinator
Dec 21, 2010 at 7:18 PM

The first error message is because of no connectivity to the SQL machine, or SQL Server instance.  The 2nd error message, after you made a change, indicates that connectivity is there, and it's able to communicate with the SQL Server instance, but the login username is not valid for the "blogengine" database.

So either the password is incorrect, which is probably not the case since you would get a different error message, I think.  The User ID "myuserid" might not be mapped correctly to the "blogengine" database, or not mapped at all.

You could check directly on the SQL machine by opening up Management Studio, and connecting to the server via this account -- myuserid/mypassword.  Once connected, try switching to the "blogengine" database to see if that works ... and if it does work, then try running a SELECT SQL statement.  My guess is that you won't be able to switch to the "blogengine" database when connected via myuserid/mypassword.

Dec 21, 2010 at 7:50 PM
Edited Dec 21, 2010 at 8:22 PM

Thanks for your response.  I am able to use Server Management Studio and connect to the remote db using the same credentials as in the web.config code.  I can drill down into the structure and modify the tables as well as get into the security table and verify that the necessary creditials are there.  I was able to open a table within the db and perform a select all list

Coordinator
Dec 21, 2010 at 9:34 PM

Glad you were able to do that.  I would suggest checking out some of the results in the Google search below.  I'm sure the answer is in one of those results.  This is basically a DB / DB/ASP.NET issue, not really BE specifically from what I can tell.  I'm pretty certain this is a configuration issue that probably only requires a simple adjustment -- just need to identify what that change is!

http://www.google.com/search?q=Cannot+open+database+requested+by+the+login.+The+login+failed+Login+failed+for+user

Dec 21, 2010 at 10:21 PM

I did not find anything that helped with the last post.  I made the neccesary changes but still am getting the unable to sign on.

This is what is currently have.

<configSections>
        <sectionGroup name="BlogEngine">
            <section name="blogProvider" requirePermission="false" type="BlogEngine.Core.Providers.BlogProviderSection, BlogEngine.Core" allowDefinition="MachineToApplication" restartOnExternalChanges="true"/>
        </sectionGroup>
    </configSections>
    <BlogEngine>
        <blogProvider defaultProvider="DbBlogProvider">
            <providers>
                <add name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core"/>
                <add name="DbBlogProvider" type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core" connectionStringName="BlogEngine"/>
            </providers>
        </blogProvider>
    </BlogEngine>
    <connectionStrings>
        <clear/>
        <add name="BlogEngine" connectionString="server=xxx.xxx.xxx.xxx;database=dbblogengine;User ID=userid;Password=upw;Trusted_Connection=no;initial catalog=blogengine;" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Coordinator
Dec 21, 2010 at 10:44 PM

Well, if the error message you're seeing is still:

Cannot open database "blogengine" requested by the login. The login failed.
Login failed for user 'myuserid'.

.... this is an error message that is coming from SQL Server -- not from BE.  It's simply saying that the "myuserid" account does not have access to the "blogengine" database.  I don't believe it's any more complex than that.

Actually, your web.config file looks good, BUT the connection string seems to have what could be the problem.  You have "database" AND "initial catalog" defined in there -- and they are different databases.  You have a database name in there of "dbblogengine" and then you ALSO have an initial catalog of "blogengine".  In case you weren't aware, "database" equals "initial catalog".  You will want to remove the duplication.  Here's a cleaned up connection string.  Just make sure the database name is correct.

Data Source=xxx.xxx.xxx.xxx;Initial Catalog=dbblogengine;User Id=userid;Password=upw;

Dec 21, 2010 at 10:46 PM
I did not find anything that helped with the last post. 

Enterprise Manager is using named pipes to connect the database engine the sqlclient (which is configured in web.config) is probably using tcpip

You need to dig in the SQL server surface area configuration and enable tcpip
Talk with an experienced dba if you're unsure what to do

There's an howto at ms support
http://support.microsoft.com/kb/914277

hth  /Peter