|
|
You're in the
sql server msde
group:Problem with "SQL Server does not exist" error
sql server msde:
db's are used and maintained from a couple of programs written in Visual Basic 6 (using an ADO connection) (these programs run on a separate computer). I've just set up a new computer and installed SQL Server Express 2005 on it. I copied the .MDF files from the MSDE unit and attached them to SQLExpress. Now, when I try to connect to the databases, I get the "SQL Server does not exist or access is denied" error. I have, of course, modified the connection string such that the Data Source property is using the correct computer name. I've looked at everything I can think of but obviously I'm missing something fundamental here. Any suggestions as to what I might be doing wrong?
On Mon, 20 Nov 2006 16:22:39 +0100, "Andrea Montanari"
[quoted text, click to view] <andrea.sqlDMO@virgilio.it> wrote: >hi Martin, >Martin wrote: >> I have a couple of databases developed and being used in MSDE. The >> db's are used and maintained from a couple of programs written in >> Visual Basic 6 (using an ADO connection) (these programs run on a >> separate computer). >> >> I've just set up a new computer and installed SQL Server Express 2005 >> on it. I copied the .MDF files from the MSDE unit and attached them to >> SQLExpress. >> >> Now, when I try to connect to the databases, I get the "SQL Server >> does not exist or access is denied" error. I have, of course, modified >> the connection string such that the Data Source property is using the >> correct computer name. >> >> I've looked at everything I can think of but obviously I'm missing >> something fundamental here. Any suggestions as to what I might be >> doing wrong? >> > >please verify the instance name is correct (in the form >ComputerName\InstanceName), the remote computer can access the lan and the >remote SQLExpress installed computer, firewall (both) have been set with the >appropriate exception(s) allowing connectivity and trafic (both the DBEngine >used port and the SQLBrowser, if used), the SQLExpress network protocols >have been enabled, the Surface Area Configuration ahs been set to allow >remote connections... Thanks, Andrea. I've gone thru your list but still no luck. * the name of the new computer is "CATServer" (it's not actually a "server" - it's running WinXP Pro with all current service packs and updates). I've tried setting the Data Source to both "CATSERVER" and "CATSERVER\SQLEXPRESS". Neither way seemed to make any difference. * the remote computer CAN access CATServer. I have one folder on CATServer shared and it can be viewed in Windows Explorer. Also, I can ping CATServer successfully. * there are no firewalls running on either computer. * the network protocols: Shared Memory, Named Pipes and TCP/IP are enabled; VIA is not enabled. * the Surface Area Configuration was something I did not know anything about. Remote Connections were set to "Local Connections Only". I changed that to "Local and Remote" (for both TCP/IP and Named Pipes) and then stopped and restarted the server service. It made no difference. I also re-booted the computer but that, too, made no difference. Any other thoughts?
On Mon, 20 Nov 2006 18:20:18 +0100, "Andrea Montanari"
[quoted text, click to view] <andrea.sqlDMO@virgilio.it> wrote: >hi Martin, >Martin wrote: >> >> Any other thoughts? >http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql Thanks again. I've gone through that fairly well but still haven't been able to get connected. I did stumble on to something though: I started the "SQL Server Browser". Now, when my VB6 program tries to connect, I'm getting a different error message: "Login failed for user 'userid'" (where userid is the userid that's in my connect string). Does this mean that SQL Server Browser has to be running in order for my program to connect? Also, when I posted earlier, I was using msado 2.5 in my VB program. One of the things in the page you referenced mentioned that I needed at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is running so I changed the reference to 2.7. That did not seems to make any difference however. Does that shed any light on the problem I might be up against?
hi Martin,
[quoted text, click to view] Martin wrote: > I have a couple of databases developed and being used in MSDE. The > db's are used and maintained from a couple of programs written in > Visual Basic 6 (using an ADO connection) (these programs run on a > separate computer). > > I've just set up a new computer and installed SQL Server Express 2005 > on it. I copied the .MDF files from the MSDE unit and attached them to > SQLExpress. > > Now, when I try to connect to the databases, I get the "SQL Server > does not exist or access is denied" error. I have, of course, modified > the connection string such that the Data Source property is using the > correct computer name. > > I've looked at everything I can think of but obviously I'm missing > something fundamental here. Any suggestions as to what I might be > doing wrong? > please verify the instance name is correct (in the form ComputerName\InstanceName), the remote computer can access the lan and the remote SQLExpress installed computer, firewall (both) have been set with the appropriate exception(s) allowing connectivity and trafic (both the DBEngine used port and the SQLBrowser, if used), the SQLExpress network protocols have been enabled, the Surface Area Configuration ahs been set to allow remote connections... -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
hi Martin,
[quoted text, click to view] Martin wrote: http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql> > Any other thoughts? -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
hi Martin,
[quoted text, click to view] Martin wrote: > > I did stumble on to something though: I started the "SQL Server > Browser". Now, when my VB6 program tries to connect, I'm getting a > different error message: "Login failed for user 'userid'" (where > userid is the userid that's in my connect string). is this all there is or does it continue with " not associated with a trusted connection"? if this is the case, you have to enable Mixed security authentication.. .you can do that at install time unchecking the "hide advanced options" check box and allowing standard SQL Server logins in the wizard or, if you provide a set of command line parameters, providing the SECURITYMODE=SQL param as well... after install time you have to use SQL Server Management Studio Express, access the server properties and, in the "security tab", enable standard authentication as well.. by default, SQLExpress installs disabling standard SQL Server authenticated connections and allowing trusted WinNT authenticated connections only... [quoted text, click to view] > > Does this mean that SQL Server Browser has to be running in order for > my program to connect? the SQLBrowser is a sort of gateway required when named instances with dynamic assigned ports are used.. by default SQLExpress installs network enabled named instance with this setting... this require the SQLBrowser to be up and running as well as it is the first point of contact between remote clients and the local server.. when a connection is requested, the SQLBrowser listening on UDP 1434 port intecepts the incoming call, resolves the actually used port and redirects the incoming call over that port... you can disable the SQLBrowser if you are running the SQLExpress instance over the "well known" port, TCP/IP 1433, the IANA assigned one to Microsoft for SQL Server use, or if you set a "static port" for it, but you have, in this latter case, to provide the used port in the connection string, like connectionstring = "Data Source=ServerName\Instancename,PortNumber; Initial Catalog=pubs; etc" [quoted text, click to view] > MDAC 2.6 at least is required for SQL Server 2000, introducing the SQL > Also, when I posted earlier, I was using msado 2.5 in my VB program. > One of the things in the page you referenced mentioned that I needed > at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is > running so I changed the reference to 2.7. That did not seems to make > any difference however. Does that shed any light on the problem I > might be up against? Server Resolution Service, the "ancestor" of SQLBrowser... but in your case it does not play a big role as you only require the client "stuff" of te MDAC stack, including ADO... on the contrary, SQLExpress requires MDAC 2.8.. -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
On Mon, 20 Nov 2006 07:58:39 -0700, Martin <martinvalley@comcast.net>
[quoted text, click to view] wrote: >I have a couple of databases developed and being used in MSDE. The >db's are used and maintained from a couple of programs written in >Visual Basic 6 (using an ADO connection) (these programs run on a >separate computer). > >I've just set up a new computer and installed SQL Server Express 2005 >on it. I copied the .MDF files from the MSDE unit and attached them to >SQLExpress. > >Now, when I try to connect to the databases, I get the "SQL Server >does not exist or access is denied" error. I have, of course, modified >the connection string such that the Data Source property is using the >correct computer name. > >I've looked at everything I can think of but obviously I'm missing >something fundamental here. Any suggestions as to what I might be >doing wrong? > >Thanks. I finally got this working - sorta... I had to do three things: I added "\SQLExpress" to the Data Source name in the client program's connection string. Also in the client program, I upgraded to version 2.8 of MSADO. On the server side of things, I added the Login that I was trying to use from the client side. With these changes, I am now able to access the data on the server as needed from the client programs. With one major exception: After I got everything working, I recompiled one of the VB programs and installed it on another computer (call it computer B for reference). But, when I run it, it encounters the exact same error condition that I had originally! IOW, this thing works from computer A but not from computer B. Yes, computer B has version 2.8 of MSADO on it (I downloaded and installed a verification tool and checked it out). Computer B has access to the server computer where SQL Express is running (they're all on the same LAN). I'm stumped. Anyone have any ideas what the problem might be?
[quoted text, click to view]
>I finally got this working - sorta... > >I had to do three things: I added "\SQLExpress" to the Data Source >name in the client program's connection string. Also in the client >program, I upgraded to version 2.8 of MSADO. On the server side of >things, I added the Login that I was trying to use from the client >side. > >With these changes, I am now able to access the data on the server as >needed from the client programs. > >With one major exception: > >After I got everything working, I recompiled one of the VB programs >and installed it on another computer (call it computer B for >reference). But, when I run it, it encounters the exact same error >condition that I had originally! IOW, this thing works from computer A >but not from computer B. > >Yes, computer B has version 2.8 of MSADO on it (I downloaded and >installed a verification tool and checked it out). Computer B has >access to the server computer where SQL Express is running (they're >all on the same LAN). > >I'm stumped. Anyone have any ideas what the problem might be? > OK, got it going... Apparently, I have to have the SQL Server Browser service running in order to connect from computer "B". Why the Browser service is not needed by computer "A", I have no idea.
hi Martin,
[quoted text, click to view] Martin wrote: > > OK, got it going... > > Apparently, I have to have the SQL Server Browser service running in > order to connect from computer "B". > > Why the Browser service is not needed by computer "A", I have no idea. SQLBrowser is not needed for local connections as they are not resolved over the TCP/IP stack but via shared memory and the port is directly resolved via local access... try modifying the network protocol (SQL Server Management Studio Express, connection dialog, "Options", "Network Protocol" = TCP/IP... you will not ba able to connect from local client as well.. -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
On Thu, 23 Nov 2006 12:09:01 +0100, "Andrea Montanari"
[quoted text, click to view] <andrea.sqlDMO@virgilio.it> wrote: >hi Martin, >Martin wrote: >> >> OK, got it going... >> >> Apparently, I have to have the SQL Server Browser service running in >> order to connect from computer "B". >> >> Why the Browser service is not needed by computer "A", I have no idea. > >SQLBrowser is not needed for local connections as they are not resolved over >the TCP/IP stack but via shared memory and the port is directly resolved via >local access... >try modifying the network protocol (SQL Server Management Studio Express, >connection dialog, "Options", "Network Protocol" = TCP/IP... you will not >ba able to connect from local client as well.. I assume by "local connections" you mean "on the same physical computer where SQL Server is running"? If so, then that is NOT the case here. Both computer "A" and computer "B" in my situation are "remote" - that is, they are separate computers on the LAN. Again, it would seem to me that if one can connect, then the other
[quoted text, click to view]
Martin wrote: > On Thu, 23 Nov 2006 12:09:01 +0100, "Andrea Montanari" > > I assume by "local connections" you mean "on the same physical > computer where SQL Server is running"? yep [quoted text, click to view] >If so, then that is NOT the > case here. Both computer "A" and computer "B" in my situation are > "remote" - that is, they are separate computers on the LAN. > > Again, it would seem to me that if one can connect, then the other > should be able to also. you can bypass the SQLBrowser if you specify the "correct" used port by the SQL Server instance... so a connection string like "Data Source=ComputerName\InstanceName,1433; Network Library=DBMSSOCN; ......" where 1433 is the TCP/IP specifi port used by the instance... are there firewall exceptions to be updated to allow remote connections from pc B (both on pc B and on the SQL Server box)? -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
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 |