Error connecting to sql 2005 express

[eUKShane]

POST APPLIES TO : WINDOWS (sql 2005 express)

1) Please make sure you have Initial Catalog=xxxxx in your connection string. You may sometimes receive an error regarding the log file. You can simply rename or remove the old log file, the application will attempt to create a new one. If it doesn't work, please try the following connection string as well:

2) Make sure you grant the folder "app_data" with read/write/execute/modify (or delete) permission on the Network Service user.

3) Make sure you only upload the .mdf file into the "app_data" folder (Do not upload .ldf file or you will receive errors saying database with the same name exists in the server).

Please make sure your connection string in your web.config is like below

[add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\ASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=ASPNETDB;" providerName="System.Data.SqlClient" /]

(If you are using a deployment project then make sure you enable web.config section replacement and put the connectionStrings section in there, like this: connectionStrings=connectionStrings.config

------------------------
Best Regards,
UKShane
http://www.eukhost.com

[Tinkerer]

I think I may need to do what is listed in this article - but having tried several approaches nothing seems to work for me.

I am trying to create a section of my web that requires login
I have all the files in the WWW Directory.
I have created a Private directory - which contains a single test page OK.aspx
I have put the login.aspx in the www directory.

on my pc (locally) I open up ok.aspx -> it redirects me to login.aspx -> I login -> I am directed back to ok.aspx.

I load the files to my domain and try it there & get

I open up ok.aspx -> it redirects me to login.aspx -> I login ->

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)

Specifically:

"Make sure you grant the folder "app_data" with read/write/execute/modify (or delete) permission on the Network Service user."

How Do I do this?? or more importantly where, which application? (I have Web Expression, Visual Studio 2005, SQL Server 2005 express)


"Quote:
[add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\ASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=ASPNETDB;" providerName="System.Data.SqlClient" /] "

This causes more errors but I think it is because I don't really understand it is this what I should have in the web.config file in the root(www) directory & do I need to edit the web.config files in the directories that I am protecting?

[swexpert]

Hi Tinkerer,
Apparently You're connecting to SQL Express which is a free limited version for use on your home PC.

Create a Database Source (DSN) in Hosting Controller with the name "LocalSqlServer" and then use those credentials.

Alternatively, create a MS SQL database (and a user for it) and use the credential in your application.

Regds
IJ

[Tinkerer]

Now I'm really confused.

Let me see if this is correct.

if I...
"Create a Database Source (DSN) in Hosting Controller with the name "LocalSqlServer" and then use those credentials. "

Do I need to add a connection string?(since this is the default user is it not?)
Do I need to use a connection string that references the odbc name?
If I ftp a file aspnetdb.mdb (which is created in sql server espress) it does not register in the databases in cpanel - only the ones created in Cpanel show so since you do not specifically reference the file path, just the database name how's that going to work?



or if I "Alternatively, create a MS SQL database (and a user for it) and use the credential in your application"

well if I create ms sql in CP with name aspnetdb then it creates aspnet_data.mdb in the DB folder - great but I need the db in the app_data file since that is where the application is looking for it - or can I just change the path in the connection string along with the user name and password setup in the cpanel.
Asn if i create the db in cpanel - do I then have to use restore to get my database into the database created in cpanel.


Has anyone out there got a working example of a ASP.NET login that works - it would be great firstly to know that it can be done & then identify the set up in each of the following

1. what is in the web.config file
2. where was the ms sql database created (in cpanel?)
3. how was the aspnetbd db from visual studio transfered into the database created in cpanel
4. what does the odbc datasource reference - the aspnetdb_data (with or without the .mdb)?

[swexpert]

Hi Tinkerer,
Are you on Windows or are you on Linux. You're referring to cpanel time and again. CPANEL is usually referred to the control panel under linux. If you're on Linux, you can't use MS_SQL database on the same platform. You will also not be able to run ASPx files under Linux not without using third party tools such as chillisoftASP. However, even with this, it won't work properly.

Ideally you should shift to Windows Hosting.

If you're on Windows Already:
a) First Create the Database, (goto Domains->Properties-->Databases->(Click on Database Name" heading to show the option to "create a database" this seems to be a bug in HC7) use MS SQL Server
b) Type a databas name. Note the Database Name: It will have a prefix like yourdomain_com_nameyouselect
c) Type a database login (this is database username) . Note again it will have yourdomain_com_ prefix
d) Enter the password twice.

Half work done! Final settings for connection to database will be:
Database Name: someprefix_com_databasenameyouselected
DB Username: someprefix_com_usernameyouselected
DB Password: WhateveryouTypedTwice
DB Hostname/Server: Set localhost or Enter your domain name or IP of your control panel (87.117.234.246)

Now onto the application part.

Here is an ASP example:
==========================
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server="LOCALHOST;" & _
"Database=someprefix_com_databasenameyouselect ed;" & _
"Uid=someprefix_com_usernameyouselected;" & _
"Pwd=WhateveryouTypedTwice;"
objConn.Close
Set objConn = Nothing
=========================

You could also use oleDB Connection method:
==========================
Provider=sqloledb;
Data Source=87.xx.xx.xx,1433;
Network Library= ;
Initial Catalog=someprefix_com_databasenameyouselected;
User Id=someprefix_com_usernameyouselected;
Password=WhateveryouTypedTwice;
Connect Timeout=4;
==========================

VB.NET (1.1 Only)
==========================
Imports System.Data.Odbc
...
Dim oODBCConnection As OdbcConnection
Dim sConnString As String = _
"Driver={SQL Server};" & _
"Server=localhost or IP ;" & _
"Database=someprefix_com_databasenameyouselect ed;" & _
"Uid=someprefix_com_usernameyouselected;" & _
"Pwd=WhateveryouTypedTwice"
oODBCConnection = New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
===========================
If no error is generated in the above examples, you got the connection!

You may also see http://www.connectionstrings.com if you wish to know more.

Hope this helps a bit.

Regds
IJ

[swexpert]

I think HC7 supports only SQL2005 Express as of now. What I mentioned above was mixed, for SQL2000 server, since I was not able to make sure whether it is SQL2005 Express or 2000 on the system.

But you should get a good idea and a lead from www. connectionstrings.com

Regds
IJ

[Tinkerer]

Thank you for that information before I continue I just want to clear one thing up.

I refer to cpanel, which I mean the hosting control panel
i.e. www. blink101.com:8077 - Is that what I should be using for setting up databases & odbc etc I have also seen :8787 refered to but this does not work for my accout.

I am on windows XP home edition, using MS Expression Web, Visual Studio WEb developer 2005 express edition, sql server express

I also have sql server tools (sql server management studio express, sql configuration manager and surface area manager) - all of which I beleive are not applicable for what I am trying to do because it is your server and I cannot (probably just as well) access it.

Also - before I start again is there any way to reset my account back it's initial state - the reason I ask is that having tried and failed to get the login working I decided to reload my original website I had been working on. This included a grid view using an access database. Now that I have reloaded my website the page no longer works & even more strange.....

I have 2 pages
each looking at a different access database
each with an odbc dns set up in the hosting control panel where the name matches exactly (ie. on my pc ACCESSDATASOURCE1 and the dns I set up = ACCESSDATASOURCE1)


.... And each give a different responce
1= It is already opened exclusively by another user, or you need permission to view its data
2= creates popup for server login!!!

Have a look for yourself
www. blink101.com/software_experience.aspx
www. blink101.com/data.aspx

[eUKhost.com]

If you want to reset your account and start from scratch then open a ticket for our support team from http://support.eukhost.com and we will recreate your account.

Let me know the ticket number once you open it as I will ask the support team to bypass verification process for reload of your account.

[Tinkerer]

when you say
b) Type a databas name. Note the Database Name: It will have a prefix like yourdomain_com_nameyouselect

Do you mean - (example uses freddy.com as the domain)


1. I type in db1 - but the actual database name will be (a. www. freddy.com_db1 or b. freddy.com_db1 .... or is underscore (_) representing \ or something else? & do I need the .mdb at the end?)

2. I type www. freddy.com_db1 as the database name when setting it up?

And should I ignore that when I set up db1 it actually creates db1_data.mdb (when I look in the folder db)

sorry it may be that I just should know more about database lingo, but I thought it best to clarify before I start to prevent difficulties.

[swexpert]

Hello Tinkerer,
I read on another post you're losing your hair. I presume you're preparing to get into modelling. Don't forget me when you become a star!

You're on Hosting Controller 6 as against 7 as I had assumed. Just to mention, I'm not working for eukhost, I'm just another user like you. Now it implies you have SQL 2000 on the server.

You can use Enterprise Manager to connect to their database as far as I know, there should not be any problem. Just enter your domain as the SQL server host/name and the DB username and password as mentioned in my earlier post.

HOWEVER, you are trying to access an ACCESS file (mdb) which the application is looking for in DATABASE folder of the web root. There is nothing concerned with SQL server here.

Change or Use this string for Jet Engine, assuming file is MS Access and not MS Access 2007 format:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Software.mdb;User Id=admin;Password=;

NOte: copy the software.mdb file to the wwwroot folder itself, just for testing, with the above example.

if your access database has a password, enter the password before the last semi-colon (Password=yourpassword; ).

This should get you working in the first go.

Regds
IJ

[Tinkerer]

I have opened ticket for account reset (unfortunately hit refresh so 2 tickets)

HVX-90647-107

CDE-10115-843

Thx

[swexpert]

Gve me a few minutes, It is a bit different on HC6,

Apologies!! I will just send you a complete instruction set.


Regds
IJ

[swexpert]

Hi again,
Well Tinkerer, I apologize for those confusing instructions. I could not make out from your posts on what server you are and what kind of connection you're seeking.

You apparently have a DSN at home PC which is working. But when you shift the files here, the DSN, obviously is missing.

I have fortunately managed to get a few screenshots.

Step1: Upload the Database File to any folder on the server, ideally in the DB folder

Step 2: Create a DSN on the server for the above file.
a) Login to Hosting Controller, Goto Domains->Properties->ODBC DSN
b) Then Click on Add DSN
c) Then Enter DSN NAme: I have named dsnSoft in the shots
Enter DSN Description as anything you like, without spaces is better.
Select Microsoft Access Driver and hit Next
d) Enter option admin and password (This is not shown in the screenshots, but i have used admin as username and admin as password as well)
e) Hit Browse and locate the file where you have uploaded the MDB file ON THE SERVER.
f) Then it should show you're ready with the Database DSN (My screenshot is showing different names but yours will show the same as entered above)

Your database DSN is now ready on the server.

IN your applications, just use the DSN Name:
DSN=dnsSoft;Uid=admin;Pwd=admin;

Now it should be a bit easier and hopefully your hair will start growing again.

Keeping my fingers crossed.

Regds
IJ

[Tinkerer]

So it's back to basics time.

ASP.NET requires database aspnetdb.mdb file in directory root\App_Data and it needs to contain the data & stored procedures created when reg_sql.exe is run to create the database.


option 1 create a database through hosting control panel called aspnetdb

Result: empty database (i.e. no tables or stored procedures) named aspnet_data.mdb created in the root\db folder (which cannot be moved)


Option 2 load database file created on my pc to App_Data directory

Result: the database is created where I want it with the name I want with the tables & procedures I want ...... but the database is NOT registered as a SQL database in either the summary list of what I have in my website nor int the database list (accessed through host control panel -> domain -> databases) & therefore I cannot set up a user & password for this database


So neither work - sorry if it is going over old ground but through speaking with people on microsoft forum it would seem that this is essential to getting the Login process running.

[swexpert]

"ASP.NET requires database aspnetdb.mdb file in directory root\App_Data and it needs to contain the data & stored procedures created when reg_sql.exe is run to create the database."

What do you mean by the above statement? Are you copying these instructions from somewhere??

MDB file is MS-ACCESS file, not SQL 2000. SQL databases are MDF - Master Data File.

You have to first clarifiy which one are you using/planning to use. Can you please post the section of the code where you're creating the connection to the database in your application?

Regds
IJ

[Tinkerer]

sorry typo - should be mdf - SQL database.

The statement you quoted was what I understant to be the requirements for ASP.NET membership login to work. I have been it contact with someone who has created a turorial on setting up the login process.

This is my full web.config file (I should add that I have tried various permutations e.g. datasource with & without the localhost)

<?xml version="1.0"?>

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<connectionStrings/>

<remove name="LocalSqlServer"></remove>
<add name="LocalSqlServer"
connectionString="Data Source=87.117.201.122/localhost;
User ID=gav23;
Password=qw34;
Initial Catalog=ASPNETDB_Data;"
providerName="System.Data.SqlClient" />
</connectionStrings>




<system.web>
<authorization>
<deny users="?" />
</authorization>
<authentication mode="Forms" />
</system.web>
</configuration>

[swexpert]

Originally Posted by Tinkerer

sorry typo - should be mdf - SQL database.

The statement you quoted was what I understant to be the requirements for ASP.NET membership login to work. I have been it contact with someone who has created a turorial on setting up the login process.

This is my full web.config file (I should add that I have tried various permutations e.g. datasource with & without the localhost)

<?xml version="1.0"?>

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<connectionStrings/>

<remove name="LocalSqlServer"></remove>
<add name="LocalSqlServer"
connectionString="Data Source=87.117.201.122/localhost;
User ID=gav23;
Password=qw34;
Initial Catalog=ASPNETDB_Data;"
providerName="System.Data.SqlClient" />
</connectionStrings>




<system.web>
<authorization>
<deny users="?" />
</authorization>
<authentication mode="Forms" />
</system.web>
</configuration>

Data Source=87.117.201.122/localhost

This should only be Data Source=87.117.201.122 i think.
--> ASPNETDB_Data Are you sure this is the dbname?Change this to whatever database name you have given to your database.

regds
IJ

[swexpert]

I can connect to the server using the username and password you have exposed above, but there is no database by the name you mentioned.

Regds
IJ

[Tinkerer]

Originally Posted by swexpert

I can connect to the server using the username and password you have exposed above, but there is no database by the name you mentioned.

Regds
IJ

2 questions then.

1 how can you connect to the server? with what?

2 the database not there, set up in control panel as aspnetdb --> which created aspnetdb_data.mdf and aspnetdb_log.ldf. should I just be using aspnetdb as the database name?

[swexpert]

Originally Posted by Tinkerer

2 questions then.

1 how can you connect to the server? with what?

2 the database not there, set up in control panel as aspnetdb --> which created aspnetdb_data.mdf and aspnetdb_log.ldf. should I just be using aspnetdb as the database name?

Yes, use aspnetdb as database name. Forget the file. However, I am still not being able to login to your database login failed for user gav23.

I can connect using Enterprise Manager, a client tool for SQL servers.

The next and last possible thing would be to allow me or someone from support access to your control panel. we will try to create a DB and try to connect. If it works, then it will confirm something misconfiguered at your end.

Regds
IJ