Can't create a ODBC DSN to a remote MSDE instance

Hello,
I need to create an ODBC DNS that points to a remote MSDE instance, but I
can only get it to work if I turn Windows Firewall off. Both the local and
remote machines are WinXP SP2 and have SQL2000 and MSDE, same domain and I
can login as the same person on each machine.
On the remote machine I've enabled TCP/IP and the default port, and I've
added that port to the firewall, but when I try to add a DSN (on the local
machine) in the ODBC control panel it says
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
I've looked at the instance via Enterprise Manager and my domain login has
admin privs, and can connect a DSN to the SQL2000 server on that machine.
Stumped.
Suggestions?
Keith
hi Keith,
Keith wrote:
> Hello,
> I need to create an ODBC DNS that points to a remote MSDE instance,
> but I can only get it to work if I turn Windows Firewall off. Both
> the local and remote machines are WinXP SP2 and have SQL2000 and
> MSDE, same domain and I can login as the same person on each machine.
> On the remote machine I've enabled TCP/IP and the default port, and
> I've added that port to the firewall, but when I try to add a DSN (on
> the local machine) in the ODBC control panel it says
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> I've looked at the instance via Enterprise Manager and my domain
> login has admin privs, and can connect a DSN to the SQL2000 server on
> that machine.
did you define an exception on the server's firewall on the MSDE used TCP/IP
port or on the service itself?
http://support.microsoft.com/default.aspx?kbid=841249
http://support.microsoft.com/default.aspx?scid=kb;[LN];841251
http://support.microsoft.com/default.aspx?scid=kb;[LN];884012 (Win2003)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> did you define an exception on the server's firewall on the MSDE used
> TCP/IP port or on the service itself?
> http://support.microsoft.com/default.aspx?kbid=841249
> http://support.microsoft.com/default.aspx?scid=kb;[LN];841251
> http://support.microsoft.com/default.aspx?scid=kb;[LN];884012 (Win2003)
Well, after some reboots and a walk around the block it works now.
I finally got it to work either with firewall port exception, or a service
exception, but neither way seemed very consistent. Making changes to the
firewall while it is running just doesn't seem to work that well; perhaps I
wasn't waiting long enough for the changes to propagate or I just need to
stop/start the firewall after changing something.
And I suspect that some of the changes that don't "require" rebooting really
do.
Anyway, thanks for the quick response and support links.
Keith

can't connect to SQL 6.5 server in different domain

Is there any trick to setting up a System DSN to connect to a SQL 6.5 Sever
that is in a different domain. The domains are trusted. I've tried every t
ype of authentication but my Win XP system just can't get a DSN to connect a
gain the SQL Server that is running Win NT4 SP6. Is there a trick?
Thanks,
ChuckHi,
Can you create a alias name using "CLIENT NETWORK UTILITY" from your WIN XP
System by providing the
1. Protocol as TCP/IP
2. Provide the SQL 6.5 IP address
3. Define the Port number
Then use the ALIAS server name to connect.
--
Thanks
Hari
SQL Server MVP
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message news:ziWkd
.18733$Rf1.10698@.newssvr19.news.prodigy.com...
Is there any trick to setting up a System DSN to connect to a SQL 6.5 Sever
that is in a different domain. The domains are trusted. I've tried every t
ype of authentication but my Win XP system just can't get a DSN to connect a
gain the SQL Server that is running Win NT4 SP6. Is there a trick?
Thanks,
Chuck|||If SQL Server 6.5 is set to use Windows only authentication, this will not
work. With SQL Server 6.5 NT authentication only works with named pipes.
Also by default SQL Server 6.5 does not enable TCP/IP. It has to be done
after setup. So use SQL Server setup to see if tcp/ip is enabled. Then set
SQL Server to use mixed security and then configure your DSN to use SQL
authentication. This is just a test to see if you can connect. With this
configuration the alias that Hari suggests should work.
What error are you getting when you try to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.

can't connect to SQL 6.5 server in different domain

Is there any trick to setting up a System DSN to connect to a SQL 6.5 Sever that is in a different domain. The domains are trusted. I've tried every type of authentication but my Win XP system just can't get a DSN to connect again the SQL Server that is running Win NT4 SP6. Is there a trick?
Thanks,
Chuck
Hi,
Can you create a alias name using "CLIENT NETWORK UTILITY" from your WIN XP System by providing the
1. Protocol as TCP/IP
2. Provide the SQL 6.5 IP address
3. Define the Port number
Then use the ALIAS server name to connect.
Thanks
Hari
SQL Server MVP
"Charles MacLean" <charlesmaclean@.sbcglobal.net> wrote in message news:ziWkd.18733$Rf1.10698@.newssvr19.news.prodigy. com...
Is there any trick to setting up a System DSN to connect to a SQL 6.5 Sever that is in a different domain. The domains are trusted. I've tried every type of authentication but my Win XP system just can't get a DSN to connect again the SQL Server that is running Win NT4 SP6. Is there a trick?
Thanks,
Chuck
|||If SQL Server 6.5 is set to use Windows only authentication, this will not
work. With SQL Server 6.5 NT authentication only works with named pipes.
Also by default SQL Server 6.5 does not enable TCP/IP. It has to be done
after setup. So use SQL Server setup to see if tcp/ip is enabled. Then set
SQL Server to use mixed security and then configure your DSN to use SQL
authentication. This is just a test to see if you can connect. With this
configuration the alias that Hari suggests should work.
What error are you getting when you try to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Cant connect to database

I am developing my first website for my boss. I am trying to connect to an access database which I have created a DSN for on the server. I am using the following code sample...

//Connection String 1

string dsn_name ="access_WALLINVEN.dsn";

string sDSNDir = Server.MapPath("_dsn");

string connectstr = ("Data Source="

+ (sDSNDir + ("\\" + dsn_name)));

//Connection String 2

string connectstr ="Data Source=/jwallpaint/_dsn/access_WALLINVEN.dsn;Uid=jwallpaint;Pwd=J2512wall;";

string commandString ="Select * from Inventory";

//Pass strings to sqlDataAdapter constructor

SqlDataAdapter dataAdapter =newSqlDataAdapter(commandString, connectstr);

//Create a dataset

DataSet dataSet =newDataSet();

//fill the dataset object

dataAdapter.Fill(dataSet,"Inventory");

//Get the table from the dataset

DataTable dataTable = dataSet.Tables["Inventory"];

//Bind to the gridview

GridView1.DataSource = dataTable;

GridView1.DataBind();

For Connection String 1 I get the error of...

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)

For Connection String 2 I get the error of...

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I am not sure which connection string to use either...

It is strange to me that I get this error talking about SQL Server when I am trying to reach my Access database.....Any Help?? Thanks...

Connection string forFile DSN:

"FILEDSN=c:\myData.dsn;Uid=myUsername;Pwd=;"

Checkhttp://www.connectionstrings.com/ for more connection strings.

Can't connect ODBC/SQLSrvr linked table from within COM+ component

I have an Access 2000 database containing an ODBC table linked to a SQL Serv
er 2000 database (using a system DSN).
No problem opening the table Using Access 2000 itself.
No problem even within a VB program (EXE) accessing that table using the fol
lowing code:
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cnSrc
.Source = "SELECT * FROM " _
& "[ODBC;" & tbODBC.Properties("Jet OLEDB:Link Provider String") & "]."
_
& tbODBC.Name & vbCrLf & "WHERE FALSE"
.CursorLocation = adUseServer
End With
On Error Resume Next
rs.Open ' <-- ERROR RAISES HERE
cnSrc is an ADO Connection with Access2000 DB
tbODBC is an ADOX Table object corresponding to the linked ODBC table
But if the same code is placed in a COM+ component (DLL ActiveX), once invok
ed by any client, when the final "rs.Open" is executed, the following error
raises:
"Connection to <sql server database name> failed"
Number = 0x80004005
SQLState = 3151
I've searched many places but with no results.
Some help will be appreciated.
Thanks in advance
RafThe Jet OLEDB provider does not support distributed transactions since it
does not implement the ITransactionJoin OLE-DB interface.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Raf" <anonymous@.discussions.microsoft.com> wrote in message
news:0377EFF9-6D50-447F-B38F-779100742A52@.microsoft.com...
>I have an Access 2000 database containing an ODBC table linked to a SQL
>Server 2000 database (using a system DSN).
> No problem opening the table Using Access 2000 itself.
> No problem even within a VB program (EXE) accessing that table using the
> following code:
> Set rs = New ADODB.Recordset
> With rs
> .ActiveConnection = cnSrc
> .Source = "SELECT * FROM " _
> & "[ODBC;" & tbODBC.Properties("Jet OLEDB:Link Prov
ider
> String") & "]." _
> & tbODBC.Name & vbCrLf & "WHERE FALSE"
> .CursorLocation = adUseServer
> End With
> On Error Resume Next
> rs.Open ' <-- ERROR RAISES HERE
> cnSrc is an ADO Connection with Access2000 DB
> tbODBC is an ADOX Table object corresponding to the linked ODBC table
> But if the same code is placed in a COM+ component (DLL ActiveX), once
> invoked by any client, when the final "rs.Open" is executed, the following
> error raises:
> "Connection to <sql server database name> failed"
> Number = 0x80004005
> SQLState = 3151
> I've searched many places but with no results.
> Some help will be appreciated.
> Thanks in advance
> Raf
>