Dear All,

[Guest]

Same problem,

Good lawd. Looks like your connection string from here. Try this one
(OleDb):

"Provider=SQLOLEDB;Data Source=xxx.xxx.xxx.xxx,nnn;Initial
Catalog=DBNAME;User ID=sqlLogin;Password=pppp;"

If you're using Integrated Security, then you need to get rid of User ID=
and Password= and add "Integrated Security=SSPI;". I think you specified
SQL Server security, however.




This is for an ODBC-style connection with a DSN set up.

 

[Guest]

I have tried, But still the same problem

Try Using this in your code...

Public Function GetConnectionString( ByVal sServer As String, _
ByVal sDatabase As String, ByVal sUserID As String, _
ByVal sPassword As String) As String

' ********** ADO Connection String ************
GetConnectionString = "Data Provider=SQLOLEDB;" & _
"Persist Security Info=False;" & _
"Data Source=" & sServer & _
";User ID=" & sUserID & _
";Password=" & sPassword & _
";Initial Catalog=" & sDatabase
End Function
' ---------------------------------------------------------

And Pass the <Server Name> in the sServer field...
IS THE SQL Server using port 1433 (the default) ??
Or some other custom Port Number ?
If it's using anything other than 1433, then you have to pass that:
as sServer = "SQLSERVERNetBiosName:nnnn" Where nnnn = port number, or
sServer = "IPADDRESS:nnnn" like "192.168.65.201:1455"

 

[Michael C#]

And you have established physical connectivity from Box B to Box A, and vice
versa? You can pull up SQL Server for Box B in Enterprise Manager on Box A?

 

[Guest]

Yes,

but note that Box B also has SQL-Server installed and IIS.

 

[Michael C#]

That's all well and good, I just want to establish physical connectivity.
So, when you registered Box B with EM on Box A, what format did you use to
register the server? Did you register it using IP address,port or by name,
or what? My point here is are you sure you have the correct Port number?
Maybe you should try the default port of 1433? Or check Box B's Server
Network Utility and verify that you do indeed have the correct Port #.

Thanks

 

[Guest]

The SQL server On A is far away from my side(phisically).
It is registered by name,..
It is using a differant porta other than the 1433, which is given a proper
access on Box A firewal..

On Box B (Which is not necessary) The SQL server &client network utility,
are also set to use the same Port as on Server A.

Please note,
That the SQL server On A is having it's own Many DB, already in use since
long time,...
And all of my need is to access One table on one DB,... Which is given to a
certain SQL Login User.

Do you think that this SQL-User should have an access to Master DB on Box A?

 

[Michael C#]

That depends on exactly what that user needs to do...

 

[Guest]

This user is a db owner on the required DB only.

That depends on exactly what that user needs to do...

 

[Michael C#]

Then my first stab at it would be no. Unless they have to access extended
stored procedures or something else in the master ...

 

[Guest]

Did we reached to a closed road?

Then my first stab at it would be no. Unless they have to access extended
stored procedures or something else in the master ...

 

[Michael C#]

We're nearing the end of the rope. It sounds like a problem with your
application or the configuration. To test this, try the following:

-Create a Windows Form app and add the following to the Form Load sub:

Dim sqlCon As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlDr As SqlDataReader
Try
sqlCon = New SqlConnection("Data Source=127.0.0.1,1433;Initial
Catalog=Northwind;User ID=USERNAME;Password=PASSWORD;")
sqlCon.Open()
sqlCmd = New SqlCommand("SELECT * FROM Orders", sqlCon)
sqlDr = sqlCmd.ExecuteReader()
While (sqlDr.Read())
Console.WriteLine(sqlDr(0))
End While
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If Not (sqlDr Is Nothing) Then
sqlDr.Close()
End If
If Not (sqlCmd Is Nothing) Then
sqlCmd.Dispose()
End If
If Not (sqlCon Is Nothing) Then
sqlCon.Close()
sqlCon.Dispose()
End If
End Try
' Replace 127.0.0.1,1433 in the connection string with your server's IP
address and Port #
' Replace the USERNAME and PASSWORD with your username and pwd

Run the app and see if it prints anything out in your Output window, or if a
messagebox pops up with an error message. I just ran this app here and it
works fine, so if your server can be reached from where you are via .NET,
this will tell us.

 

[Guest]

As I told previously,
It is working for any desktop apllication either from .,...

My real application is very simple and does not do more than excuting a
Stored procedure.
My problem is in openning the ADO connection.

 

[Michael C#]

As I told previously,
It is working for any desktop apllication either from .,...

My real application is very simple and does not do more than excuting a
Stored procedure.
My problem is in openning the ADO connection.

OK, that's all I can do for you then.

Assuming your Connection String is at fault, the code below would be able to
prove that rather quickly and easily, by actually connecting to your remote
server using a connection string that has been tested and works. If the
code below *didn't* work, then the connection string could be eliminated as
a suspect and you could move on to checking other things, like you IIS
configuration, for instance. At any rate, best of luck .

 

[Guest]

I believe that you are running into a problem with delegation. If you are
unfamiliar with this concept, please review this link since Keith Brown can
explain it far better than I can:

http://www.pluralsight.com/wiki/default.aspx/Keith.GuideBook/WhatIsDelegation.html

Unless you have changed the defaults, your web server is probably running as
ASPNET (pre-Server 2003), or as Network Service (Server 2003). Based upon
your connection strings, it looks like you are trying to use Windows
Authentication / Integrated Security, which means that either ASPNET or
Network Service is the identity that you are using to access your database
server. If this is true, then you need to grant the ASPNET identity from
your web server access to SQL Server.

It is also possible that you are trying to use web.config to impersonate
another identity. However, unless you are using Server 2003 and enabling
delegation (this feature is not available in earlier versions of Windows),
then your impersonation is only valid on your web server. You are not
allowed to use an impersonated identity to access a remote machine, such as
your database server. If you want to do this, make sure that you are using
Windows Server 2003 and turn this feature on for your web application.

If you have everything set up properly, try this connection string for
integrated security:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=<server>;Initial
Catalog=<database>;

I'm not 100% sure of the correct syntax for the <server> if you need to
specify a non-default port, but I'd try standard TCP/IP syntax first:
"10.16.175.255:1234".

A much simpler solution would be to simply use SQL Server authentication.
Simply pass your user id & password (without any of the Integrated Security
parameters) in your connection string. This will not be as secure as
Windows authentication, but it will work across servers.

This connection string should work for SQL Server authentication:

Provider=SQLOLEDB.1;User ID=<userid>;Data
Source=<server>;Pwd=<password>;Initial Catalog=<database>;

Hopefully this will put you on the right track.

- Erik Wynne Stepp

 

[Guest]

just a thought... If the SQL server you're attempting to connect to was
installed as an MSDE instance and, during installation, you did not include
the command line parameter "DISABLENETWORKPROTOCOLS=0 " the SQL instance will
reject any attempt to connect from a network.

 

[Peor]

I have exactly the same problem. I have machine A running the SQL serve
and machine B running the SQL Server, IIS and ASP.NET application. Ther
is impossible to connect to the machine A SQL server database from th
ASP.NET app on machine B. However, the same code runs fine when use
from the Windows desktop (.net) application.

Moreover, the same code ran fine even from ASP.NET app until
installed the SQL Server SP3 and Analysis Services on the machine B
After that installation something got broken.

The C# code:

string sConString = "server=ServerB;database=DBOnB;uid=Peor;pwd=Help";
SqlConnection sqlCon = new SqlConnection( sConString );
sqlCon.Open(); <<<< IT FAILS HERE WHEN CALLED FROM ASP.NET APP BU
RUNS FINE WHEN CALLED FROM DESKTOP AP


-
Peo

 

[Michael C#]

Can you connect from server A to server B using QA? I'm doing the exact
same thing right now, except that I have 4 connections going simultaneously
from a single ASP.NET app. to 4 different SQL Servers, including a local
instance, all with SP 3a installed.

Did something change with your network protocols or other settings before,
during, or after the SP 3 installation? Look at the Server Network Utility
and Client Network Utility to ensure you're using the same protocols, ports,
etc.