• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

A network related or instance specific SQL Server problem

Hi there;

I think I have a problem with my MS SQL server Express...A network related or instance specific SQL Server problem..I configured SQL authentication and when i test the connection no problem...but when i run the website from .NET or IE, I got that error...

Please examine the screenshots...
pro1.jpg
pro2.jpg
0
jazzIIIlove
Asked:
jazzIIIlove
  • 11
  • 10
  • 2
  • +1
1 Solution
 
RiteshShahCommented:
well, the error you are getting in first screen shot is not related to SQL Server, need some settings in IIS.

have a look at my article at

http://www.sqlhub.com/2009/04/unable-to-start-debugging-on-web-server.html

for second screen shot, can you please show me your connection string?
0
 
RiteshShahCommented:
for your second error, following links could be helpful,

http://www.mydigitallife.info/2007/10/31/error-has-occurred-while-establishing-a-connection-to-sql-server-2005-which-does-not-allow-local-and-remote-connections/

http://forums.asp.net/t/1334649.aspx

if it will not solve problem, kindly let me see your connection string.
0
 
jazzIIIloveAuthor Commented:
I have done the first step which is
-- It will open one dialog box and you will have to move to Directory Security tab.
-- Click on Edit button under Authentication and access control
-- Select check box of integrated Windows Authentication, click Ok

for second error: I tried for the links but no joy...

when i run the website project from VS 2008 .NET, I stuck in connection opening code in my project...I configured my db in server explorere and test the connection and there is no problem while testing...But when i run, the code cannot open the connection...

Please examine my screenshot, regarding sql configuration manager...

pro.jpg
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
jazzIIIloveAuthor Commented:
My connection string in weconfig file:
<add key="strConn" value="Data Source=localhost;database=ALUMNI;User id=root;Password=123123;"/>
            <!--<add key="strConnMySQL" value="Driver={MySQL Odbc 5.1 Driver};Server=localhost;Database=test;uid=root;pwd=abcdef;stmt=set names 'latin5';"/>-->
            <add key="strConnMySQL" value="Data Source=localhost;database=test;User id=root;Password=abcdef;"/>
            <add key="accountInitialYear" value="1998"/>
            <add key="passwordNumericLength" value="5"/>
            <add key="passwordNonNumericLength" value="2"/>
            <add key="passwordTotalLength" value="7"/>
            <add key="loginURL" value="http://localhost:1035/AlumniProject/Login.aspx"/>
      </appSettings>
      <connectionStrings>
            <add name="ALUMNIConnectionString" connectionString="Data Source=localhost;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=abcdef" providerName="System.Data.SqlClient"/>
      </connectionStrings>
0
 
RiteshShahCommented:
try using this connection string.

Data Source=.;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123;
0
 
St3veMaxCommented:
This looks to be your issue:

<add key="strConnMySQL" value="Data Source=localhost;database=test;User id=root;Password=abcdef;"/>

Specifically: strConnMySQL...

This is SQL Server; Not MySQL.

String should be something like:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

HTH
0
 
RiteshShahCommented:
St3veMax,

as long as key concern, does it make any difference if he will use "strConnMySQL"? thats a key, not a part of connection string, so that he can use anything. as long as connection string concern than I have already gave it with his specific example.
0
 
St3veMaxCommented:
I dont know specificially with .NET as I'm a DBA; but to me; if you're using the wrong driver; it may explain why things wont work.

Another suggestion would be to go into SQL Server Configuration Manager; Disable Named Pipes and Enable TCP/IP

HTH
0
 
jazzIIIloveAuthor Commented:
RiteshShah:

>>Data Source=.;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123

done...same problem exists...

I go into surface area configuration is SQL Server Surface Area Configuration, click on local and remote connections radio button and i changed using both TCP/IP and named pipes into using TCP/IP connections only...I restart the server but no joy...

By the way, I have server management Studio Express and i had logged in with Windows authentication and then create user as root for the db and i can login there now as SQL authentication...

Another thing is, this webconfig is working perfectly in another machine...I had the code from that friend (he had used mysql then changed his mind and switch to MSSQL) (So, that webconfig is working in that machine)

I have given the pass as abcdef...This is a dummy password...so there is no problem with passwords in webconfig...

Examine the screenshot...

Best regards..
problem-goes-on...no-joy....JPG
0
 
RiteshShahCommented:
below are two connection string, can you please use one by one, do check your id and pass in that.

Data Source=CTIS-4295774425\SQLEXPRESS;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123


OR

Data Source=.\SQLEXPRESS;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123




0
 
jazzIIIloveAuthor Commented:
Please examine the screenshot:

When i authenticate with windows auth.There is a content to see in tables...But when i authenticate with MSSQL auth., there is no content...Why is that?
problem-goes-on2...no-joy2....JPG
0
 
RiteshShahCommented:
because the user you have used to login with sql authentication, doesn't have rights to see database.
0
 
RiteshShahCommented:
login with windows auth. in your sql server and set rights for your user shown in attached screen shot and try to run your .NET application with that user in connection string.
errorlogin.JPG
0
 
jazzIIIloveAuthor Commented:
RiteshShah:

No joy...

below are two connection string, can you please use one by one, do check your id and pass in that.

Data Source=CTIS-4295774425\SQLEXPRESS;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123


OR

Data Source=.\SQLEXPRESS;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123

0
 
RiteshShahCommented:
now, I am sure there is no permission to root, set it as per my above screen shot
0
 
jazzIIIloveAuthor Commented:
but I have a root user also...I had created by your help...No change for root user in manager?
0
 
jazzIIIloveAuthor Commented:
so, i am going to set permission for root not for TUser?
0
 
RiteshShahCommented:
yes, I don't have root user/login. BTW, it is login, not a user. be sure you set permission for login
0
 
jazzIIIloveAuthor Commented:
ok...

I set the permission for root login as you said...and now, i can see the tables in my sever manager and in my .net server explorer...

but my problem still exists...

I revert the change in tcp/ip into tcp/ip with named pipe in my surface conf...

I had changed the following add name tag...with . or CTIS-42... or localhost...

<add name="ALUMNIConnectionString" connectionString="Data Source=CTIS-4295774425\SQLEXPRESS;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123" providerName="System.Data.SqlClient"/>

I have restarted the mssql services...But no joy...what to do?

Best regards...
0
 
RiteshShahCommented:
now, I am thinking you have placed connection string at wrong place in web.config.

use below connection string and put it under  tab of your web.config



or



or



and use that connection string in your .aspx.cs page like

SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["Connection"]);
myConnection.Open();

you can use your connection name instead of "myConnection".

let me know.
0
 
jazzIIIloveAuthor Commented:
there is the add name and add key tags and i am little confused...I am not really sure where to edit....

I tried to fix but again no joy...I am sending you the original config file...

Coudl you fix for me and send it?

web.config.txt
0
 
RiteshShahCommented:
bellow is your web.config, now use your connection string in your .CS file like:

SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["strConn"]);
myConnection.Open();
<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<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="strConn" value="Data Source=CTIS-4295774425\SQLEXPRESS;Initial Catalog=ALUMNI;Persist Security Info=True;User ID=root;Password=123123"/>
		<add key="accountInitialYear" value="1998"/>
		<add key="passwordNumericLength" value="5"/>
		<add key="passwordNonNumericLength" value="2"/>
		<add key="passwordTotalLength" value="7"/>
		<add key="loginURL" value="http://localhost:1035/AlumniProject/Login.aspx"/>
	</appSettings>
	<connectionStrings />
		
	<system.web>
		<globalization uiCulture="en" culture="en-GB" />
		<!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
        -->
		<compilation debug="true">
			<assemblies>
				<add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
				<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
				<add assembly="System.Configuration.Install, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
				<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
			</assemblies>
		</compilation>
		<!--
            The <authentication> section enables configuration 
            of the security authentication mode used by 
            ASP.NET to identify an incoming user. 
        -->
		<authentication mode="Forms"/>
		<authorization>
			<deny users="?"/>
		</authorization>
		<!--
            The <customErrors> section enables configuration 
            of what to do if/when an unhandled error occurs 
            during the execution of a request. Specifically, 
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.
 
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
		<pages>
			<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 verb="*" path="*.asmx"/>
			<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
			<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" 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"/>
		</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>

Open in new window

0
 
jazzIIIloveAuthor Commented:
You are genius!
0
 
millhouse11Commented:
Hello RiteshShah,

I have the same issue and have not been able to resolve it.

I can see you are an expert in this area.

I have posted my question here:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24510133.html

I do have two connection strings:

1. inside <appSettings> for referencing from inside the project

2. another inside  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=....

that one was created when adding a datagrid/datasource.

Could that be the problem? thanks in advanced for your help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 11
  • 10
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now