datasource on remote SQL server?

Disclaimer: I'm a web developer, not a DBA or network admin, so if the following is a totally stupid question, please be gentle--

I am trying to come up with a somewhat decent testing environment (they have no staging area) for a client for whom I'm converting a static HTML site to ASP pulling content from SQL Server.

I'm running Windows 2000 Professional at home and its slightly-watered-down version of IIS webserver, which is fine for testing ASP that doesn't talk to a database. My question is, is there any way that my home webserver can talk to their production SQL Server installation?

My working setup is this--I can connect via Remote Desktop to their webserver machine, which has a copy of Enterprise Manager installed, which has a database registration for a SQL Server installation on a physically separate machine. Given those couple of degrees of separation, I have no idea how locked-down the actual SQL Server box might or might not be as far as the outside world goes.

So, several questions:

Is there a way, without access to the machine console, to figure out if the SQL Server box has an Internet IP address I can try to set up as an ODBC DSN?

Given access to Enterprise Manager for that SQL Server install, are there settings I can check and/or configure to make this happen?

And if this is the sort of thing I wouldn't be able to resolve myself, is there anything specific I can ask the client's tech staff to see if I can get what I need?

I hope this is coherent. If the answer is "dude, you need to talk to the network admin" I'll do that... it's just I'd always rather learn how to do something myself when it's possible :)Since you can get a terminal session going between yourself and them, it sounds like you can get onto their network. If you are using the webserver's name to connect to the webserver, then you are using their DNS, and you are practically all set.

begin rant()
As a rule, no IP addresses should be used in code, except for raw
testing. Any server should be addressed by it's DNS or other network
name. This way, if an IP address changes, or a machine is replaced,
the application is not affected.
end rant;

If you can not use a DNS name (I have seen some VPN connections forbid using others' DNS Servers) for the SQL server on your local machine, and you must use IP addresses, I would say ping the name of the SQL Server, and see what comes back. Then ping that address from your local machine to make sure that you can get there.

As for how to connect to the DB itself, you will have to talk to their administrator. Ideally you should get a read-only login for the particular database you want to connect to. Many small shops use the sa login for all of their apps, but that is not very secure.

Hope this helps.|||Hi, thanks for your response.

I had tried creating a new datasource using the SQL server machine name while connected via terminal session to the webserver. I get an error:

Connection failed:
SQLState '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL Server not found.

I feel your pain on IP addresses in code, but I only want it to try to create a datasource on my home machine, since creating a datasource using the machine name doesn't seem to be working. Pinging the SQL box from the webserver console just gets me an internal network IP; a ping of that IP from my home machine (with terminal session running) times out.

In any event, using an external IP, if it exists, would seem preferable to a machine name for my purposes, because it means I don't have to keep a remote desktop session open 24/7 for the next month.

I have no problem connecting to the DB when using Enterprise Manager from the webserver console--it's set up to use Windows authentication--I believe I could ask for & receive SA access if it were necessary though.|||When you get to the second screen of the ODBC Configuration (where it asks for a user and password), click on the Client Configuration button. See if you can change the NetLib to TCP/IP. Sometimes the NetLibs can be a little funny about actually finding a server. This would be on the client's webserver, of course.|||I did try that, in spite of not knowing what the heck I was doing--figured I couldn't break what wasn't work in the first place. No love, though--thanks for the suggestion.

My client is using <a href="http://links.10026.com/?link=http://www.neoteris.com/products.html">Neoteris</a> to manage remote sessions. I don't know much about the technical details of how it works (and because their support pages require a customer id and password, it seems they don't WANT me to know) but I found a diagnostic button to click and I can see where it is mapping a number of machine names on their network to IP addresses in my home network's internal space. I see the webserver machine name there; I don't see the SQL box machine name.

I broke down and emailed my contact to ask if they can set up remote access authorization to the SQL box too--I still don't like the notion of having to keep the Neoteris session (based in a java applet in a web browser) running 24/7. It's gonna be fun the first time my DSL hiccups and the client calls me all "I can't connect to the test site" and I'm on the other side of town--but it would be a solution.

Databases in Remote Servers

Hello,

This is my first post, and i am looking for help about Databases in remote servers.

This is my problem:

1) I have a free account in "HostBasket", with 50 mb of space, and 10 mb of SQL Server... with ASP.NET 2 support --> http://www.hostbasket.com/dotnet2.shtml?LinkFrom=10060

2) They gave me this info for the database:

Your SQL Server: sql15.hostbasket.com
Your database name: myDatabaseName
Your loginfor the database: myLogin
Your passwordfor the database: myPassword
Connectionstring for ASP.NET (using ADO.NET with System.Data.SqlClientnamespace):
Persist Security Info=True;User ID=myLogin;Password=myPassword;Initial Catalog=MyDatabaseName;Data Source=sql15.hostbasket.com

3) Actually, i don't know what to do with that info... I mean, i have read so much info, and all people saying "their way" to do things... oh, I am so confused...

4) I read in a website, this: "the database can be uploaded as a FILE saved in the App_Data folder, under the .mdf extension"

I read that from : http://dotnetjunkies.com/QuickStartv20/aspnet/doc/data/vwd.aspx

5) In the url given above, there is a "step by step" example... i follow it, exactly (i start a asp.net website, i create the database and the table, i show the data with the grids, etc )

6) All is OK until here, i mean, when i run the website with VWD, it shows me the info OK.

7) But... ?How can i do this works on the Remote Server i described in 1) and 2) ?

I know how to upload my website, in fact i uploaded a very basic one, and all was OK, but when i work with databases starts my problem...

So, i am almost sure, that i have an error in the "Conecction String"...

connectionString="Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|\MiBaseDeDatos.mdf;
Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

?How should it be, to work with my free remote server?

or,

if you have done the "easy way" of uploading the .MDF file in the server (but a server different of mine) please, tell me the "how to..." (and the server you used :p )

-------

Ok guys, i am a Chilean young (i dont speak english very well) and i tried to write my ideas in an easy way, so I REALLY HOPE you can help me.

I am a New (very new) on this (vwd , .net , asp.net ) in fact, i am using this just 3 days ago...

THANK YOU SO MUCH,

Carlos,
La Serena, Chile.

Do you know which version of the SQL server hostbasket is using?

1> I am not sure whether you can connect to the datbase and sql server (sql15.hostbasket.com) during your development. So you still can use what you are using for your development.

2> But when you upload your site to hostbasket, you want your site use the database/sqlserver they have provided you rather than you data file.

To take advantage of the database/sql server that is provided you, here are something you need to do:

1> define two connection strings in you web.config file and give them the same name, e.x.:

<

connectionStrings>

<!-- Dev-->

<addname="myDB"connectionString="Server=.\SQLEXPRESS;Integrated Security=True;Database=MyDataBase"providerName="System.Data.SqlClient"/>

<!-- Prod-->

<!--

<add name="myDB" connectionString="Data Source=sql15.hostbasket.com; UID=myLogin; PWD=mypassword; Initial Catalog=mydatabase;" providerName="System.Data.SqlClient"/>

-->

</

connectionStrings>

In this example, I have defined two database connection strings with the same name. One is for development environment, and the other one is for production. When I move the website to the production, I will comment out the DEV string and uncomment the PROD string.

2> Then in the code, whenever I need to reference my connection string, I will simply read it out from the web.config:

stringconnStr =ConfigurationManager.ConnectionStrings["myDB"].ConnectionString;

3> When you move your web site to the production(hostbasket.com), you also need to move what ever you have done in your database to the production (your database on sql15.hostbasket.com).

This should make your web site runs smoothly in the production.

Hope this helps. If you have any confusions, please let me know.

|||Hello chunshahab,thank you SO MUCH for the reply :)

I have understood all what you say, but i have some questions still...

let's start:

- According what i understood you, i have "two ways" for working with databases:
a) Uploading the .MDF database created on App_Data folder
b) UPDATING the database that they GAVE ME (sql15.hostbasket.com)

I want to use any way (the first that works OK, hehehe...)

- I understood what you said about the two connection strings, thanks for the tip ;)

- QUESTION: you said i have to move my website to the production (hostbasket.com), and that i have to move what ever i did in my local databaseTO the production (my database on sql15.hostbasket.com) ...BUT How can i do that ??

Because there is no option like "upload your database" in the control panel of my hostbasket account...

- I read somewhere that i need a "sql client" and i tried SquirreL but i actually don't understand it... so the database on the server is still empty ^^U

So, please, tell me how to "update" the database they gave me (sql15.hostbasket.com)

Thank you so much, really...

Carlos.

Postdata: i don't know what version of SQL server they have... they just promote this"ASP.net 2.0Beta 2"

Postdata 2: I am working with VWD Express 2005


|||

Yes. You need a SQL Client to connect to your database server and update your database.

You can download a free version ofSQL Server Management StudioExpress (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en) and use it to connect to your database server.

I know you can use it to connect to any edition of SQL Sever 2005. I have not tried to see whether it can connect to SQL Server 2000 or earlier. Check with hostbasket and ask them which version of SQL serve rthey are using.

Once you can connect to your database, they you can run the scripts to generate the tables, stored procedures which you have created with your database file.

|||Thanks again,

I am downloading the 40 MBs right now...

I have a new Question:

- I have created my table with Visual Web Designer (yep, while i am developing the web site) ...

?there is a way to create automatically the script, according the changes i have made in my database? ( i hope you understand what i said... my english is not so good)

--> I mean, if i created the database and the tables in a "using mouse way", and then create the script automatically without i have to be writting "create table, insert xxx, etc"

Thanks|||Chunshahab:

Thanks for All your help , now i can access the database on the remote server, see the info, edit it, etc.

THANKS,
Carlos.|||

Carlos,

Glad everything worked out for you. What is version of hostbasket's SQL server? Can you use the SQL Server Management Studio connect to it?

Have a great week.

|||Hello,

sorry i missed this post ^^U

I don't know about the version of SQL server in hostbasket, but it works 100% ok with me (using .net 2.0 )

And i connected to the database on hostbasket through SQL Server Management Studio, and all was 100% OK.

Thanks again,

Carlos.