July 14, 2008 at 12:40 am
Apologies if I am being dumb, but I am new to SQL 2005.
I am in the process of moving from SQL Server 2000 to 2005 Express and now have both running on my pc. I am able to access both databases locally from my VB application.
When I try to access them over the network from my laptop using the same connection strings, I can access the 2000 one, but not the 2005 one.
The connection string I'm using is to connect with 2005 Express:
Provider=sqloledb;Data Source=MYPC\SQLEXPRESS;Initial Catalog=MyDB;User Id=MyUserName;Password=MyPassword
(This is the same as the connection string I'm using for the working 2000 connection, except the Data Source doesn't have 'SQLEXPRESS' after it.)
I have mixed authentication set and have set 2005's Surface Area Configuration for Remote Connections to accept both TCP/IP and named pipes.
Any help would be much appreciated.
Thanks, Jo.
July 14, 2008 at 12:45 am
Hi,
Have you installed the SQL 2005 Native Client driver on your laptop ?
That would probably account for why you can see 2000 but not 2005.
Regards,
Ian.
July 14, 2008 at 12:46 am
Yep this sounds like you will have to install SQLnative client for 2005.
Regards.
Terry
July 14, 2008 at 1:16 am
Hi Ian
Thanks for that, I wasn't aware I needed to install this. I've now done so and changed the connection string's Provider to include 'sqlncli' instead of 'sqloledb' (was that the right thing to do? sqloledb still wasn't working) but now get a new error -
"Error locating server/Instance specified [xFFFFFFFF. ]"
Do I need to change the syntax of the connection string to use the new driver?
July 14, 2008 at 1:19 am
Hi,
Try
http://www.connectionstrings.com/?carrier=sqlserver2005
I think you need to change source to server.
Regards,
Ian.
July 14, 2008 at 1:55 am
hmmm, I tried changing 'Data Source' to 'Server', as well as trying 'Database' instead of 'Initial Catalog' and 'Uid' instead of 'User ID'; none of these made a difference.
Does anyone know if 2005 needs me to manually open any ports in my firewall to let connections in from the LAN? Bearing in mind the 2000 connections already work. Also that my 2005 connection string works if I run it locally, not on the LAN. If ports are to blame, does anyone know which one/s, or how I can find out?
Or any other ideas out there?
July 14, 2008 at 10:38 am
do you have a firewall enabled?
if so check the firewall log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 14, 2008 at 6:39 pm
Thanks for your help everyone. It is working now, hooray!!
I checked the firewall log & found lots of DROP UDP entries coming through so started fiddling. It seems my mistake was due to restarting the sql service the wrong way after configuring the Surface Area. I originally restarted the service afterwards by opening up Services through Admin Tools and manually stop/starting "SQL Server (SQLEXPRESS)" instead of doing it actually inside the Surface Area Configuration Manager. Now I have used the manager to restart the service it seems to be working. Not sure what the difference is but I guess the config manager does something helpful to the firewall as now I'm not getting the DROP UDPs?? Does anyone know?
Next is to get it working remotely over the internet. Ugh, wish me luck... no doubt I will be back here for more assistance soon!!
Jo
July 17, 2008 at 10:54 am
jsgault (7/14/2008)
Thanks for your help everyone. It is working now, hooray!!
well done!!
jsgault (7/14/2008)
I checked the firewall log & found lots of DROP UDP entries coming through so started fiddling. It seems my mistake was due to restarting the sql service the wrong way after configuring the Surface Area. I originally restarted the service afterwards by opening up Services through Admin Tools and manually stop/starting "SQL Server (SQLEXPRESS)" instead of doing it actually inside the Surface Area Configuration Manager. Now I have used the manager to restart the service it seems to be working. Not sure what the difference is but I guess the config manager does something helpful to the firewall as now I'm not getting the DROP UDPs?? Does anyone know?Next is to get it working remotely over the internet. Ugh, wish me luck... no doubt I will be back here for more assistance soon!!
Jo
always using config manager to make mods to your sql instance(s)
the firewall logs are always a dead giveaway
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply