Resolved: Connecting to a remote SQL Server

[tom_hotspur]

Hiya

Basically i've developed a VB.net/SQL Server 2k app locally on my machine.

In a few month time the system will be tested. This requires the SQL Server to be installed on a server so others can access it over the WAN with their VB.net clients.

obviously all users will require rights to access the server but i do not wish to map the drive on their local machine (as they do not require to see the server just to use it as an application server).

Therefore i plan to use the absolute path in the connection string.

Here is where i get confused...

the users are obviously not logged into the server when they first boot up in the morning, so when they attempt to access the sql server wih the client does this mean they have to log in to the server as well as the sql server?

If so how do i achieve this?

Also am i right in calling this 'accessing a remote sql server'? or
is this classed as somthing else?

any help would be great!

[crptcblade]

Your connection string should have the IP address of the server in it, or the text address. I'm having a brain fart as to what its actually called, but like "mysqlserver.mydomain.com".

[Mike Hildner]

To add on to that, when you access SQL Server it's usually a TCP connection, so you don't need to map a drive or anything. The IP address or the friendly name will work for the server name.

You connect to SQL Server either by Windows Authentication or SQL authentication. Windows authentication, IMHO is best, because otherwise you're sending your username and password in plain text.

[tom_hotspur]

Thanks for the help Mike,

a few questions....

Originally posted by Mike Hildner
The IP address or the friendly name will work for the server name.

What do you mean by friendly name? could you explain perhaps with an example?

[Mike Hildner]

Sure. First I should say that BOL (books online) is your friend - it's included in the install and there's a wealth of information there. Grab your favorite beverage and dig in

Here's an example I use to connect to MSSQL:

Public Function CreateConnection() As SqlConnection
        Dim conn As SqlConnection = New SqlConnection
        conn.ConnectionString = "Persist Security Info=False;" & _
                        "Integrated Security=SSPI;" & _
                        "database=Dispatch;" & _
                        "server=MIKELAP"
 
        Return conn
    End Function

I have a machine that's called "MIKELAP", but I could have used an ip address there as well.

A "friendly name", if I', using the correct terminology, is just the human-friendly name that resolves to an ip address - i.e. vbforums.com rather than 63.236.73.220

HTH,
Mike

[tom_hotspur]

Thats great, thanks for you for your help mike.

I figured it must be something along those lines but wasn't too sure.

Mucho appreciated mate.

[robertx]

I tried to connect using:

conn.ConnectionString = "Persist Security Info=False;" & _
"Integrated Security=SSPI;" & _
"database=Dispatch;" & _
"server=MIKELAP"

Replacing the database name and replacing MIKELAP with the IP address specified by typing "ipconfig /all" from the command prompt. I am running MSDE on the same computer as .NET.

It is returning an error saying SQL Server not found or access is denied.

I tried this also without success:


conn.ConnectionString = "Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"

I don't remember specifying a username when I installed MSDE. Is the username the computer's name?"

[Mike Hildner]

Just to make sure I replaced my machine name "MIKELAP" with 127.0.0.1 and it connected fine. I've always used windows security and do not specify a user name/password.

Not sure if the connection string is different for MSDE, I wouldn't think so. It looks like your second connection string is for Access, but not sure as I don't use that.

Can you connect to MSDE with anything? The command line tools that ship with it allow you to set up users/permissions etc, although it's easier with the GUI tools that come with SQL server.

Try to connect with isql.

At least with SQL server, when you install you choose either windows or sql permissions and (if I remember right) you specify a password for "sa".

[robertx]

I have only been able to connect to MSDE locally using:

"Data Source=(local);Initial Catalog=Test;Integrated Security=SSPI"

If I use a connection string that specifies an IP address (either IP or 127.0.0.1), it won't connect.

I downloaded a program called DbaMGR2k which is like a visual enterprise manager for MSDE. It's pretty much like .NET's Server Explorer except that you are able to manager usernames and passwords (grant/deny access etc). That confirmed that the'sa' username did not have a password associated but I still wasn't able to connect using "User ID=sa;Pwd=".

I assume that you can't connect remotely to MSDE and that this is one of its restrictions. Has anyone done so successfully?