ASP Authentication problem

lukin

I have as ASP site which uses authentication. It was working fine but now it is not. In web.config, I have the following:

<authentication mode="Forms">  
     <forms name="form1" loginUrl="AccessDenied.aspx" />  
      </authentication>

and further down I have this:

 <location path="DeleteHotel.aspx">  
    <system.web>  
       <authorization>  
         <!--<deny users="?"/>-->  
        <deny roles="General"/>  
       </authorization>  
    </system.web>  
   </location>  

It means that when a user logs in it checks their role and if they are in role "General" they are denied access to "DeleteHotel.aspx" and redirected to "AccessDenied.aspx".
It is working fine on my local machine; "AccessDenied.aspx" is displayed when a user in role "General" tries to go to the "DeleteHotel.aspx" page but on the live one it delays for a while and then displays the following page;
"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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)..."
It is obviously as SQL Server problem but it must only be with authentication as I have other SQL connections working fine.
I am wondering is it something to do with the "App_Data" folder as that folder was not on the live site but is on the local one.
It contains an mdf file "ASPNETDB" which I copied over from the local siteto the live one but it didn't work.
When ASP performs authentication does it go to this mdf file? If so I may need to re-create all the authorisation tables, procedures etc. again (I have a script to do this).

MrDarcy

Looks like a problem with your SQL connection string. You need to work out which SQL instance you are using, you sound a bit unsure as to where your DB actually is, which would imply that you are not too sure which SQL server instance you are trying to connect to...

amen

Scott http://weblogs.asp.net/scottgu/ has a post on this issue and a solution some where on his blog

lukin

MrDarcy wrote: »

Looks like a problem with your SQL connection string. You need to work out which SQL instance you are using, you sound a bit unsure as to where your DB actually is, which would imply that you are not too sure which SQL server instance you are trying to connect to...

No, I know which instance I want to connect to; it's the one on the server, not the one in App_Data. I found a link here which might help me:
http://www.studiocoast.com.au/knowledgebase/6/aspnet/using-sql-server-instead-of-aspnetdbmdf.aspx

lukin

I have unistalled all the roles and memebership tables, procedures etc. by running the ASP.NET SQL Server setup wizard (aspnet_regsql.exe). I selected the "remove application services from an existing database" option and then selecting the live database.

Once this is done I run aspnet_regsql.exe again and select "Configure SQL Server for application services" to create everytjing again but it fails on the "Install Roles.sql"

Exception:
An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 446 and the SqlException message is: Cannot resolve collation conflict for equal to operation.
Cannot resolve collation conflict for equal to operation.


Does the aspnet_regsql.exe command just run the SQL Server query files that are in the folder C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727?

If so I should be able to edit them to correct the errors and then run aspnet_regsql.exe again?