Problems connectiong to SQL Server 2008 R2 Express via remote .VBS script
-
I have a .VBS script which has been running on my local PC and connecting to a shared SQL Server 2008 fine using the server IP. I've moved to a dedicated server running SQL Server 2008 R2 Express and I can nolonger connect via my .VBS script to this server using "IP" or "IP/sqlexpress" or "IP:1433/sqlexpress" or "IP\DS-15041\SQLEXPRESS" or "IP:1433\DS-15041\SQLEXPRESS".
I can connect from my local PC via SQL Server 2008 Express EM just using the dedicated server IP so firewall and port 1433 on the dedicated server all seem to be open and working. telnet IP1433 also connects ok.
My .VBS script runs fine on the dedicated server using "IP/sqlexpress" or just "./sqlexpress"
My server support say it might be a limitation of SQL Server 2008 R2 Express.
Can anyone confirm this or suggest other tests or solutions?
Thanks.
-
By default SQLExpress does not allow remote connections, so you need to go into Server Configuration Manager and Enable remote connections by enabling Named Pipes and/or TCP/IP in the network configuration section.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack Corbett (4/13/2011)
...you need to go into Server Configuration Manager and Enable remote connections by enabling Named Pipes and/or TCP/IP...This has already been done which is why I can telnet ok and can connect via a local copy of EM. I'm not sure this is configured correctly however and it maybe the cause of the problem, but it does allow connections on port 1433.
-
Okay, that is the most common reason. The second reason is often Windows Firewall. You need to make sure the proper ports are open in the firewall. Here's an article on how to do that, http://msdn.microsoft.com/en-us/library/cc646023.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
The fact that you can telnet to port 1433 from a remote machine but cannot reach it using the instance name from a remote machine says to me that the "SQL Server Browser" service may not be running.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Can you connect if you specify the port in your connection? ServerName\Instance,Port
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
SQL Server Browser is running.
I've tested with the following and can't connect
IP\SQLEXPRESS,1433
IP\SQLEXPRESS 1433
IP\SQLEXPRESS:1433
IP:1433
(not quite sure how to add the port)
Firewall has MSSQL added under port 1433 and the setting appear to be fine.
-
can you post your vbscript code or at least the connection string?
Also you have not posted the error you getting, so can you post that as well?
Thanks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
If you specify the port you do not want to specify the instance name. IIRC SQL Server Native Client treats instance name with a higher precedence so in supplying them both it will ignore the port number. Try one of these:
SERVER_NAME,PORT
IP,PORT
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Error messages:
srv = IP\SQLEXPRESS,1433
"SQL Server does not exist or access is denied"
srv = IP
"Object required"
srv = IP\SQLEXPRESS
"SQL Server does not exist or access is denied"
srv = "IP"
uid = "user_name"
pwd = "pwd"
db = "db_name"
sqlConn = "Driver={SQL Server};Server=" & srv & ";Database=" & db & ";Uid=" & uid & ";Pwd=" & pwd & ";"
function execSql(sql,conn)
if conn = "" then
conn = "sqlConn"
end if
on error resume next
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open(conn)
objConnection.Execute(sql)
If Err.Number <> 0 then
subject = "Feed Error: " & fso.GetBaseName(WScript.ScriptName)
errorReport = errorReport & "COM Error Number: " & Err.Number & vbCrLf
errorReport = errorReport & "Brief Description: " & Err.Description & vbCrLf
errorReport = errorReport & "SQL: " & sql & vbCrLf
wscript.Echo("ERROR:" & vbCrLf & errorReport)
else
errorReport = "OK"
End If
objConnection.Close
execSql = errorReport
end function
execSql "DELETE FROM myTable",sqlConn
-
You are using the wrong driver. Try this connection string:
"Driver={SQL Server Native Client 10.0};Server=" & srv & ";Database=" & db & ";Uid=" & uid & ";Pwd=" & pwd & ";"
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (4/14/2011)
You are using the wrong driver. Try this connection string:"Driver={SQL Server Native Client 10.0};Server=" & srv & ";Database=" & db & ";Uid=" & uid & ";Pwd=" & pwd & ";"
Tried that with
IP
IP\SQLEXPRESS
IP:1433
and all give the same error:
Com Error Number: 424
Brief description: Object required.
My original connect string (post above) gives different errors when using IP\SQLEXPRESS. Not sure what that suggests.
-
Object required means the instance of the COM object could not be created. Please verify that the 2008 SQL Native Client has been installed on the remote machine.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Does SQL Server Native Client just need to be on the machine that is running MSSQL Server?
If so it should be install by default I believe.
"Microsoft SQL Server Native Client 10.0 is installed when you install SQL Server 2008 R2 or the SQL Server tools."
http://msdn.microsoft.com/en-us/library/ms131321.aspxc
I'm not completelty sure how to check if it's installed. I see SQL Server Native Client 10.0 Driver within ODBC Data Source Admin on the SQL Server machine. Does that mean it's installed? If so it's on local and remote machines.
-
paul-941374 (4/14/2011)
Does SQL Server Native Client just need to be on the machine that is running MSSQL Server?If so it should be install by default I believe.
"Microsoft SQL Server Native Client 10.0 is installed when you install SQL Server 2008 R2 or the SQL Server tools."
http://msdn.microsoft.com/en-us/library/ms131321.aspxc
I'm not completelty sure how to check if it's installed. I see SQL Server Native Client 10.0 Driver within ODBC Data Source Admin on the SQL Server machine. Does that mean it's installed? If so it's on local and remote machines.
It needs to be on all machines accessing SQL Server...i.e. for you the remote machine you're trying to run the VBS script on. Check the ODBC applet on the remote machine and see if you see it in the Driver tab.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply