Authentication, SQL and ASP.NET

8 posts, 0 answers
  1. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 25 Jun 2008 Link to this post

    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
  2. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts

    Posted 25 Jun 2008 Link to this post

    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
  3. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 25 Jun 2008 Link to this post

    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
  4. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts

    Posted 25 Jun 2008 Link to this post

    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
  5. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 25 Jun 2008 Link to this post

    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> 
     
  6. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts

    Posted 25 Jun 2008 Link to this post

    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:

    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
  7. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 26 Jun 2008 Link to this post

    Hi Veskoni,

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

    Greetings,
    Daniel
  8. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts

    Posted 26 Jun 2008 Link to this post

    Hi Daniel,

    I found these two articles which I believe will help you resolve the problem:
    Greetings,
    Veskoni
    the Telerik team

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