Authentication, SQL and ASP.NET

[Daniel Plomp]

Hi all,

This is maybe a topic I should know about, but everytime I face this problem, I just try to understand it, but I don't :)

I have just finished my ASP.NET application (.NET 3.0, r.a.d.controls and SQL Server 2005).
Everything works fine on my development machine, of course. But now I want to deploy everything to a production machine and here is where the problems start. Well, at least in this situation.

Setting up IIS 6.0 etc. and creating the website is going okay. No problems. Setting up the SQL 2005 database is going okay.

Running the app and connecting to the database is a problem. The user is not allowed to login.

Now I have given dbo.owner rights to a domain user. This domain user is also the user I use in my connectionstring.

What more do I have to do, to let this domain user have access to my database?
I always thought that having a user and password inside the connectionstring was enough to get a connection (at least locally that works!?). I know about the worker process running on a different account etc. but to me it seems that this isn't the problem in this case?

Could anybody give me some advice on how I should configure my web.config, sql server etc? I tried to many options and I'm a bit lost now...

Thanks,
Daniel

[Veselin Vasilev]

Hello Daniel,

I guess you have detached the database from your development machine and attached it to the production server or you have used "restore from backup" way.
In either way, I recommend the following:

1. Open SQL Management Studio and connect to your database.
2. Expand your database and then the Security / Users nodes.
3. Check if the domain user you want to use for connecting is there. If it is - delete it.
4. Now expand the Security node of the database server (which is different from the Security node of your database)
5. Go to Logins and add the Windows user account there. The Login name should look like this: YourDomain\YourUserName
6. Go to User Mappings settings of this user and check the appropriate database from the right top pane. After that check the db_owner from the right bottom pane.
7. Make sure your connection strings connects to the appropriate server ( YourDomain\YourDBServer ) with the correct user credetentials.

I hope this helps.

Sincerely yours,
Veskoni
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Daniel Plomp]

Hi Veskoni,

Thanks for the reply. Appreciate the fast anwser!
I have one question about your point no. 7:

What would be the format of this Domainname\Servername?

Format 1:

<add name="Eight88ConnectionString" connectionString="Data Source=DS03;Initial Catalog=<Database>;User ID=<User>;Password=<password>" providerName="System.Data.SqlClient" />

With this format I'm getting this error:
Login failed for user 'GLOBALPOINT\svc-80808-authuser'.

Format 2:

<add name="Eight88ConnectionString" connectionString="Data Source=GLOBALPOINT\DS03;Initial Catalog=<Database>;User ID=<User>;Password=<password>" providerName="System.Data.SqlClient" />

With this format I'm getting this error:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Anyway, I know that the server allows remote connections, the ASP.NET application is running under the DefaultAppPool.

Daniel

[Veselin Vasilev]

Hi Daniel,

Actually YourDomain\YourDBServer will not work because it will try to connect to the YourDomain server and locate the YourDBServer SQL instance which is not correct in your case.

So, you need to use DS03. Just make sure what is the SQL server instance name - is it the default one?
If yes, you do not need to specify anything else, just double check the password, if the user has Granted access (check the Status property) and the correct User Mappings.

Also you can try this:

Data Source=tcp:DS03,1433;Initial Catalog= ...

or put the IP address of the DB server just to make sure the DNS is resolving correctly:

Data Source=tcp:10.0.0.101,1433;Initial Catalog= ...

If you have a named SQL instance, then you need to connect using:

Data Source=DS03\YourSQLInstanceName;Initial Catalog = ....


Let me know how it goes.

Sincerely yours,
Veskoni
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Daniel Plomp]

Hi Veskoni,

I tried all the option, but none of them were succesfull.
I'm wondering if I have to give the computer account also access to SQL server?

You see, that is where I'm a bit confused, on how and when you need to give one or another account access to your database.

Ehmm... do you have more ideas? I'll paste my web.config in here, just to see if there is anything I'm missing:

<?xml version="1.0"?>
<configuration>
    <configSections>
        <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
            <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                    <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere"/>
                    <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                    <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                    <section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                </sectionGroup>
            </sectionGroup>
        </sectionGroup>
    </configSections>
    <appSettings>
  <add key="AdDomain" value="XXXXX" />
  <add key="AdIp" value="XXXXX" />
  <add key="AdUsername" value="XXXXX" />
  <add key="AdPassword" value="XXXXX" />
  <add key="AdFriendlyName" value="XXXXX" />
  <add key="AdAuthGroup" value="XXXXX" />
  <add key="MaxRegsPerTimeSpan" value="5" />
  <add key="MailBcc" value="XXXXX" />
  <add key="MailFromAddress" value="XXXXX" />
  <add key="MailFromName" value="XXXXX" />
  <add key="MailServer" value="XXXXX" />
 </appSettings>
    <connectionStrings>
    <add name="Eight88ConnectionString" connectionString="Data Source=tcp:DS03,1433;Initial Catalog=80808;User ID=XXXXX;Password=XXXXX" providerName="System.Data.SqlClient" />
 </connectionStrings>
    <system.web>
        <customErrors mode="Off" />

        <compilation debug="false" />
        <authentication mode="Forms">
            <forms loginUrl="~/Admin/Login.aspx" name="adAuthCookie" timeout="10" path="/"/>
        </authentication>
        <authorization>
            <allow users="?"/>
        </authorization>
        <pages styleSheetTheme="Eight88">
            <controls>
                <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            </controls>
        </pages>
        <httpHandlers>
            <remove path="*.asmx" verb="*"/>
            <add path="*.asmx" verb="*" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
            <add path="*_AppService.axd" verb="*" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
            <add path="ScriptResource.axd" verb="GET,HEAD" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
            <add path="ChartImage.axd" verb="*" type="Telerik.Web.UI.ChartHttpHandler, Telerik.Web.UI, Version=2008.1.515.35, Culture=neutral, PublicKeyToken=121fae78165ba3d4" validate="false"/>
        </httpHandlers>
        <httpModules>
            <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </httpModules>
    </system.web>
    <system.codedom>
        <compilers>
            <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
            <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="OptionInfer" value="true"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
        </compilers>
    </system.codedom>
    <!--
        The system.webServer section is required for running ASP.NET AJAX under Internet
        Information Services 7.0.  It is not necessary for previous version of IIS.
    -->
    <system.webServer>
        <validation validateIntegratedModeConfiguration="false"/>
        <modules>
            <remove name="ScriptModule"/>
            <add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </modules>
        <handlers>
            <remove name="WebServiceHandlerFactory-Integrated"/>
            <remove name="ScriptHandlerFactory"/>
            <remove name="ScriptHandlerFactoryAppServices"/>
            <remove name="ScriptResource"/>
            <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ChartHandler" path="ChartImage.axd" verb="*" type="Telerik.Web.UI.ChartHttpHandler, Telerik.Web.UI"/>
        </handlers>
    </system.webServer>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

[Veselin Vasilev]

Hi Daniel,

I am shooting in the dark here. 

Please have a look at the following articles and let me know if they are helpful:

• Understanding "login failed" (Error 18456) error messages in SQL Server 2005
• SQL Server 2005: Login failed for user

Also, you can try connecting with the "sa" user.

Check if a firewall prevents the incoming packets on port 1433 on the DB server.

Sincerely yours,
Veskoni
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Daniel Plomp]

Hi Veskoni,

If I use a normal SQL User (not attached to a Windows User) then everything is okay. Very strange...

Greetings,
Daniel

[Veselin Vasilev]

Hi Daniel,

I found these two articles which I believe will help you resolve the problem:

• http://msdn.microsoft.com/en-us/library/ms998292.aspx
• http://bytes.com/forum/thread269427.html

Greetings,
Veskoni
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center