| Groups | Blog | Home |
sql server msde : Access Denied on trying to connect
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
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 [quoted text, click to view] "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 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 [quoted text, click to view] "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
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 [quoted text, click to view] "Frank" <Frank@discussions.microsoft.com> wrote in message news:207BFCBD-1ED7-46A9-B140-C06746927E26@microsoft.com... > 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 >> >>
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
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |