Still unable to connect to SQL database on non-development machine

[Guest]

Hi

I have still not been able to sort out the issue of a non-development machine not being able to open a connection to an SQL database.

Aaron, if you read this thanks for any help you can/could give me

The details are as below

My connection code is (using VB.net):

myConnection = New SqlConnection("data source=SPEEDY;initial catalog=OpsAdminTest;integrated security=True"

MessageBox.Show("connection string: " + myConnection.ConnectionString
MessageBox.Show("myConnection.Database: " + myConnection.Database
MessageBox.Show("myConnection.DataSource:" + myConnection.DataSource
MessageBox.Show("myConnection.Workstationid:" + myConnection.WorkstationId
Tr
myConnection.Open(
Catch ex As Exceptio
MessageBox.Show("Error opening connection"
End Tr
MessageBox.Show("finished initialization"

This is done immediately after the InitializeComponent() call in the application

When I compile/release the code for installation and then install on the same machine that I developed it on, it works fine.
But when I install on the non-development machine, it fails to open the connection

Are you using DSN, if so, is there an appropriate DSN connection on the non development machine

The non-development machine has the SystemDSN connection to the OpsAdminTest database as it is an active database that we use for test developement of other services. My company does interactive voice services for various tv/radio/magazines and we use SQL to maintain the services

Are you using Windows or SQL authentication

We are using windows authentication for the SystemDSN connection to the database

Have you used SQL Profiler to see if you are even touching the SQL server

Sorry this question eludes my limited knowledge. How would I use SQL Profiler in my code to see if I am connection to the server

Again, thanks for any help

 

[Gerry O'Brien [MVP]]

I do believe if you are using a DSN, then your connection string should
simply say;

myConnection = "DSN=Speedy"

If that is your DSN.

I have to ask if Speedy is the local SQL server on the development machine
or if it is another server.

--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

Hi,

I have still not been able to sort out the issue of a non-development

machine not being able to open a connection to an SQL database.

Aaron, if you read this thanks for any help you can/could give me.

The details are as below:

My connection code is (using VB.net):

myConnection = New SqlConnection("data source=SPEEDY;initial

catalog=OpsAdminTest;integrated security=True")

MessageBox.Show("connection string: " + myConnection.ConnectionString)
MessageBox.Show("myConnection.Database: " + myConnection.Database)
MessageBox.Show("myConnection.DataSource:" + myConnection.DataSource)
MessageBox.Show("myConnection.Workstationid:" + myConnection.WorkstationId)
Try
myConnection.Open()
Catch ex As Exception
MessageBox.Show("Error opening connection")
End Try
MessageBox.Show("finished initialization")

This is done immediately after the InitializeComponent() call in the application.

When I compile/release the code for installation and then install on the

same machine that I developed it on, it works fine.

But when I install on the non-development machine, it fails to open the connection. non development machine?

The non-development machine has the SystemDSN connection to the

OpsAdminTest database as it is an active database that we use for test
developement of other services. My company does interactive voice services
for various tv/radio/magazines and we use SQL to maintain the services.

We are using windows authentication for the SystemDSN connection to the database. server?

Sorry this question eludes my limited knowledge. How would I use SQL

Profiler in my code to see if I am connection to the server?

 

[Guest]

Hi Gerry

"I have to ask if Speedy is the local SQL server on the development machine or if it is another server.

Thanks for replying. SPEEDY is a separate SQL server.

I do believe if you are using a DSN, then your connection string shoul

simply say

myConnection = "DSN=Speedy

So I should change my code as follows

myConnection = NewSqlConnection("DSN=Speedy"
myConnection.Database = "OpsAdminTest

Thanks for the help.

 

[Guest]

Gerry

Just able to try your suggestion (myConnection = "DSN=SPEEDY") and it failed to run on the development machine.

Also, as the myConnection.Database property is read only I can't set the value to be the Database name we need which is OpsAdminTest. We have different databases that do different things. So, for development we use OpsAdminTest, when we go live with a service/application, we would use OpsAdmin

Any other suggestions?

 

[Gerry O'Brien [MVP]]

Sorry, forgot to tell you that the Speedy in the DSN=Speedy, should be the
name of the DSN, not the server. My mistake.



--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

Gerry,

Just able to try your suggestion (myConnection = "DSN=SPEEDY") and it

failed to run on the development machine.

Also, as the myConnection.Database property is read only I can't set the

value to be the Database name we need which is OpsAdminTest. We have
different databases that do different things. So, for development we use
OpsAdminTest, when we go live with a service/application, we would use
OpsAdmin.

 

[Guest]

Tried using the following connection strings and results as indicated

1st entry: This is my original connection string
myConnection = New SqlConnection("data source=SPEEDY;initial catalog=OpsAdminTest;integrated security=True"

Connects correctl

2nd attempt: Modified with your suggestions

myConnection = New SqlConnection("data source=OpsAdminTest;initial catalog=OpsAdminTest"

Fails to connect to database

So, it appears that the only way to connect to the server is by using the 1st connection string. Any other ideas/suggestions?

 

[Gerry O'Brien [MVP]]

No, you need to have a DSN created on the machine that will be using the
program that points to the correct datasource.

One of the problems with DSNs is distribution of apps that use them. The
configuration can be hard to get right.

Let's simplify it and stick with your original connection string.

One of the first things I would check next is to verify that the computer
with the app installed, that is not the dev machine, can actually resolve
the computer name of SPEEDY. If you cannot find the computer on the
network, then the connection string will fail for sure.

If you are using TCP/IP as the protocol, on the client machine, type in PING
SPEEDY and see if you return an IP address. if so, then you are resolving
the name correctly and there is likely an authentication issue.

What is the exact error message you are receiving on the client?

--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

Tried using the following connection strings and results as indicated:

1st entry: This is my original connection string.
myConnection = New SqlConnection("data source=SPEEDY;initial

catalog=OpsAdminTest;integrated security=True")

Connects correctly

2nd attempt: Modified with your suggestions.

myConnection = New SqlConnection("data source=OpsAdminTest;initial catalog=OpsAdminTest")

Fails to connect to database.

So, it appears that the only way to connect to the server is by using the

1st connection string. Any other ideas/suggestions?

 

[Guest]

The client machine can see the server. Pinging Speedy returns an IP address. We use the OpsAdminTest database to do development work on our IVR system. So, as the client machine is within the IT department, it has access to the database currently through Access using a System DSN.

The error message I get is: Error opening connectio

from the "Catch as Exception section at the start (immediately after the Initialise Component() call.

Is there any other way to test the actual error return code so that we can see it

thanks

----- Gerry O'Brien [MVP] wrote: ----

No, you need to have a DSN created on the machine that will be using th
program that points to the correct datasource

One of the problems with DSNs is distribution of apps that use them. Th
configuration can be hard to get right

Let's simplify it and stick with your original connection string

One of the first things I would check next is to verify that the compute
with the app installed, that is not the dev machine, can actually resolv
the computer name of SPEEDY. If you cannot find the computer on th
network, then the connection string will fail for sure

If you are using TCP/IP as the protocol, on the client machine, type in PIN
SPEEDY and see if you return an IP address. if so, then you are resolvin
the name correctly and there is likely an authentication issue

What is the exact error message you are receiving on the client

--
Gerry O'Brie
Visual Developer .NET MV
Visual Basi

Tried using the following connection strings and results as indicated
myConnection = New SqlConnection("data source=SPEEDY;initia

catalog=OpsAdminTest;integrated security=True"1st connection string. Any other ideas/suggestions

 

[Gerry O'Brien [MVP]]

Well, the exception class has numerous properties that will return
information to you. You can use MessageBox.Show or Debug.Writeline, your
choice, and gain access to the properties in this manner.

Try
DB connection code here
Catch ex as Exception
MessageBox.Show(ex.Message) or you can use ErrorCode, InnerException or
Source.

Now, Exception is just a plain Exception object but each one of the data
providers has its own exception properties.

--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

The client machine can see the server. Pinging Speedy returns an IP

address. We use the OpsAdminTest database to do development work on our IVR
system. So, as the client machine is within the IT department, it has
access to the database currently through Access using a System DSN.

The error message I get is: Error opening connection

from the "Catch as Exception section at the start (immediately after the Initialise Component() call.

Is there any other way to test the actual error return code so that we can see it?

thanks.

----- Gerry O'Brien [MVP] wrote: -----

No, you need to have a DSN created on the machine that will be using the
program that points to the correct datasource.

One of the problems with DSNs is distribution of apps that use them. The
configuration can be hard to get right.

Let's simplify it and stick with your original connection string.

One of the first things I would check next is to verify that the computer
with the app installed, that is not the dev machine, can actually resolve
the computer name of SPEEDY. If you cannot find the computer on the
network, then the connection string will fail for sure.

If you are using TCP/IP as the protocol, on the client machine, type in PING
SPEEDY and see if you return an IP address. if so, then you are resolving
the name correctly and there is likely an authentication issue.

What is the exact error message you are receiving on the client?

--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

Tried using the following connection strings and results as indicated:
myConnection = New SqlConnection("data source=SPEEDY;initial

catalog=OpsAdminTest;integrated security=True") using the
1st connection string. Any other ideas/suggestions?

 

[Guest]

Thanks, Gerr

The MessageBox.Show(ex.Message) response was

"The .Net Data SQL Provider (System.Data.SqlClient) requires Microsfot Data Access Components(MDAC) version 2.6 or later.

I will try to download and install this component on the client machine. If this fails to correct the problem, I will let you know

I appreciate all the help you have given me

debbi

----- Gerry O'Brien [MVP] wrote: ----

Well, the exception class has numerous properties that will retur
information to you. You can use MessageBox.Show or Debug.Writeline, you
choice, and gain access to the properties in this manner

Tr
DB connection code her
Catch ex as Exceptio
MessageBox.Show(ex.Message) or you can use ErrorCode, InnerException o
Source

Now, Exception is just a plain Exception object but each one of the dat
providers has its own exception properties

--
Gerry O'Brie
Visual Developer .NET MV
Visual Basi

The client machine can see the server. Pinging Speedy returns an I

address. We use the OpsAdminTest database to do development work on our IV
system. So, as the client machine is within the IT department, it ha
access to the database currently through Access using a System DSN

The error message I get is: Error opening connectio
from the "Catch as Exception section at the start (immediately after th Initialise Component() call
Is there any other way to test the actual error return code so that we ca see it
thanks
----- Gerry O'Brien [MVP] wrote: ----
No, you need to have a DSN created on the machine that will be usin

th
program that points to the correct datasource

One of the problems with DSNs is distribution of apps that use them

Th
configuration can be hard to get right

Let's simplify it and stick with your original connection string
One of the first things I would check next is to verify that th

compute
with the app installed, that is not the dev machine, can actuall resolv
the computer name of SPEEDY. If you cannot find the computer on th
network, then the connection string will fail for sure

If you are using TCP/IP as the protocol, on the client machine, typ

in PIN
SPEEDY and see if you return an IP address. if so, then you ar resolvin
the name correctly and there is likely an authentication issue

What is the exact error message you are receiving on the client
--

Gerry O'Brie
Visual Developer .NET MV
Visual Basi

Tried using the following connection strings and results a indicated
myConnection = New SqlConnection("data source=SPEEDY;initia

catalog=OpsAdminTest;integrated security=True" using th
1st connection string. Any other ideas/suggestions

 

[Gerry O'Brien [MVP]]

You're most welcome Debbie, hopefully it will solve your problem.

--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

Thanks, Gerry

The MessageBox.Show(ex.Message) response was

"The .Net Data SQL Provider (System.Data.SqlClient) requires

Microsfot Data Access Components(MDAC) version 2.6 or later."

I will try to download and install this component on the client machine.

If this fails to correct the problem, I will let you know.

I appreciate all the help you have given me.

debbie

----- Gerry O'Brien [MVP] wrote: -----

Well, the exception class has numerous properties that will return
information to you. You can use MessageBox.Show or Debug.Writeline, your
choice, and gain access to the properties in this manner.

Try
DB connection code here
Catch ex as Exception
MessageBox.Show(ex.Message) or you can use ErrorCode, InnerException or
Source.

Now, Exception is just a plain Exception object but each one of the data
providers has its own exception properties.

--
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic

The client machine can see the server. Pinging Speedy returns an

IP
address. We use the OpsAdminTest database to do development work on our IVR
system. So, as the client machine is within the IT department, it has
access to the database currently through Access using a System DSN. after the
Initialise Component() call. that we can
see it? be using
the

program that points to the correct datasource.

use them.
The

configuration can be hard to get right.

the

computer
with the app installed, that is not the dev machine, can

actually

resolve
the computer name of SPEEDY. If you cannot find the computer on the
network, then the connection string will fail for sure.

machine, type
in PING

SPEEDY and see if you return an IP address. if so, then you

are

resolving
the name correctly and there is likely an authentication issue.
Gerry O'Brien
Visual Developer .NET MVP
Visual Basic message

catalog=OpsAdminTest;integrated security=True") source=OpsAdminTest;initial
catalog=OpsAdminTest") using the
1st connection string. Any other ideas/suggestions?