I've installed the latest MSDE version Re1A and MSDE Manager (from
Vale Software) and SQL works fine on the workstation on which msde is
installed. But when i try to create a connection with another
workstation, the connection fails.
I've installed msde on WinXP Pro SP2 with the following command
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
I could not install mdac 2.8 as it is a part of SP2 which i've
allready installed.
If i try to telnet ipadres 1433, i get:
Can not connect to host, connection failed.
Uninstall the firewall didn't help a bit.
When i setup the connection variables like
Servernaam (i use an ip-adres)
SQL username en password
I can't select a database
If i test the connection, i get an error MS Data Link Error:
[DBNETLIB][ConnectionOpen(Connect()).] The SQL server does not exist
or access denied.
I've also to set the Network Library to dbmssocn, but no luck with
that.
MSDE Manager also can't connect form the client workstation. MSDE
Manager works fine on the host.
What am i missing?
PLEASE Help me
Frank
It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
Server machine are successful because on the Server a SHARED MEMORY
connection is being made. You can verify what netlibs SQL is listening on
by looking at the latest SQL Errorlog (defaut location is c:\program
files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
line in this error log which states:
SQL server listening on TCP, Shared Memory, Named Pipes.
If you only have Shared Memory on this list, then only local connections
are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
only local connectivity is permitted, by default. If you want to permit
remote connections to SQL Server Desktop Engine, follow the steps in the
following article:
814130: How to help secure network connectivity for SQL Server 2000 local
databases
http://support.microsoft.com/?id=814130
Also, check these KB's:
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
ID: 841249
http://support.microsoft.com/?id=841249
How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
841252
http://support.microsoft.com/?id=841252
You may not be able to connect to an instance of SQL Server that is - ID:
841394
http://support.microsoft.com/?id=841394
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Fany, Thanx for your reply,
It did help me get 1 step closer to have it working.
I can define a connection and test it succesfully. But when i try to do the
same with two different third party software the connection doesn't work
properly.
With MDSE manager; the program can't get a connection running, it can't get
the information of the sql host.
And practically the same happens with the second tool.
Why is it possible to test the connection succesfully but it doesn't allow
its content to be reviewed? I know I have all the usernames and passwords
correct.
I hope you can point me again in the right direction...
gtz Frank
"Fany Vargas [MSFT]" wrote:
> It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
> Server machine are successful because on the Server a SHARED MEMORY
> connection is being made. You can verify what netlibs SQL is listening on
> by looking at the latest SQL Errorlog (defaut location is c:\program
> files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
> line in this error log which states:
> SQL server listening on TCP, Shared Memory, Named Pipes.
> If you only have Shared Memory on this list, then only local connections
> are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
> only local connectivity is permitted, by default. If you want to permit
> remote connections to SQL Server Desktop Engine, follow the steps in the
> following article:
> 814130: How to help secure network connectivity for SQL Server 2000 local
> databases
> http://support.microsoft.com/?id=814130
> Also, check these KB's:
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
> ID: 841249
> http://support.microsoft.com/?id=841249
> How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
> 841252
> http://support.microsoft.com/?id=841252
> You may not be able to connect to an instance of SQL Server that is - ID:
> 841394
> http://support.microsoft.com/?id=841394
>
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||Hi Frank,
Sorry, came late to this one and it may have been discussed before but:
1. Try connecting with the servername "(local)". This will use shared
memory.
2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
3. Try checking you don't have XP SP2 firewall enabled. If so and you want
to use tcp, you'll need to set up an exception for it.
4. Make sure you have at least MDAC 2.7 installed.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:207BFCBD-1ED7-46A9-B140-C06746927E26@.microsoft.com...[vbcol=seagreen]
> Fany, Thanx for your reply,
> It did help me get 1 step closer to have it working.
> I can define a connection and test it succesfully. But when i try to do
> the
> same with two different third party software the connection doesn't work
> properly.
> With MDSE manager; the program can't get a connection running, it can't
> get
> the information of the sql host.
> And practically the same happens with the second tool.
> Why is it possible to test the connection succesfully but it doesn't allow
> its content to be reviewed? I know I have all the usernames and passwords
> correct.
> I hope you can point me again in the right direction...
> gtz Frank
> "Fany Vargas [MSFT]" wrote:
|||Hi Greg,
Thanx for your reply,
1. using msde locally is no problem, this works fine
2. allready got that enabled
3. I got Symantec Internet Security 2003 installed and made a firewall rule
to allow full communication on ports 1433 and 1434
4. MDAC 2.8 can't be installed because MDAC is a part of SP2
I try making a connection to the server with: telnet servername 1433
But i get an error (translated): No connection can be made to the host on
port 1433. Connection failed.
To be sure is wasn't Symantec that was blocking traffic, i disabled the
firewall. But no luck there.
Is there something else i can check?
thanx,
Frank
"Greg Low [MVP]" wrote:
> Hi Frank,
> Sorry, came late to this one and it may have been discussed before but:
> 1. Try connecting with the servername "(local)". This will use shared
> memory.
> 2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
> 3. Try checking you don't have XP SP2 firewall enabled. If so and you want
> to use tcp, you'll need to set up an exception for it.
> 4. Make sure you have at least MDAC 2.7 installed.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
|||When you state "I can define a connection and test it succesfully. ", do
you mean from a remote machine? How did you "test and define" the
connection? For example did you create an ODBC DSN and that tested
successfully? If telnet failed then, etheir SQL is NOT listening on TCP,
there's a firewall blocking sql port 1433, or sql may not be listening on
port 1433 and may instead be listening on a different port.
1. Is SQL a default or a named instance?
2. Test the following commands from both a local and a remote server, if
sql is a named instance then in the OSQL commands below replace
<ipaddressofsqlmachine> with <ipaddressofsqlmachine\INSTANCENAME> and
replace <sqlserver_machine_name> with <sqlserver_machine_name\INSTANCE_NAME>
telnet ipaddressofsqlmachine 1433
telnet sqlserver_machine_name 1433
ping -a ipaddressofsqlmachine
ping sqlserver_machine_name
osql.exe -Stcp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Stcp:ipaddressofsqlmachine,1433 -E -Q"select getdate()"
osql.exe -Sipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Slpc:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Snp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Ssqlserver_machine_name -E -Q"select getdate()"
3. What does the latest sql errorlog (file named ERRORLOG with no
extension) state sql is listening on? Does it say tcp, shared memory and
named pipes? This errorlog will also state what port sql is listening on.
4. It seems the problem is SQL isn't listening on TCP. So please ensure
that SQL is listening on TCP (you can use the sql server network utility or
sql errorlog to do this)
5. Also, below is a good article on how to troubleshoot these kinds of
issues: How to troubleshoot connectivity issues in SQL Server 2000 - ID:
827422, http://support.microsoft.com/?id=827422
Best of luck!
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
