Tuesday, March 27, 2012
Connecting to SQL Server 2000 thru a VPN?
I'm coding a VB.NET WinForms app and an ASP.NET app that will access a MS
SQL 2000 Server. I've been told that when I move into a "production"
environment that I must go thru a VPN. I'm no VPN expert, but this doesn't
make much sense to me especially since the remote clients running my VB.NET
WinForms app may not have teh VPN setup.
If VPN can be used as a tunnel to access a SQL 2000 Server, can someone tell
me what needs to be in place on the client side and what would I use for a
connection string to get to the SQL 2000 Server thru the VPN.
I can't find any info on MSDN that even mentions how to connect to a SQL
2000 Server thru VPN.
Thanks, Rob.
Hi Rob,
The VPN session needs to be established prior to executing your VB.NET
app. There's not a specific connection string to do this.
Here's a sample kb article on how to setup a VPN with our firewall server ,
ISA Server.
837355 How to configure a VPN server by using Internet Security and
http://support.microsoft.com/?id=837355
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Tuesday, March 20, 2012
Connecting To Remote MSDE With Server Management Studio
I am having issues connecting to an MSDE instance on a remote machine
using MSSQL 9's Server Management Studio.
I am also have issues connecting to a remote instance of MSSQL v7 using
Server Management Studio. Later, I will have to connect to MSSQL v8.
If there is a step-by-step guide somewhere, I would like a URL for it.
Otherwise, I would just like to have input regarding what I should be
checking.
The MSDE was installed without an instance name, so I believe the
instance name is the name of the machine. I believe that I need to
specify the machine and the instance name in the Server Management
Studio connection dialog. I believe the format is
MachineName\InstanceName
In all cases I am using TCP/IP to connect.
All authentication is SQL Auth. The MSDE machine is configured to use
SQL Auth. The MSSQL v7 machine is too.
I was a bit surprised to see the requests on port 1434 instead of 1433.
I'll learn about the reason(s) for this later. Right now, I want to
know if there are any considerations regarding this.
There are firewalls involved, they have been configured to allow
access. This is confirmed by being able to use the MSSQL v7 tools to
connect to both the MSDE and the v7 servers.
The error I receive when connecting to the MSDE machine is:
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) (Microsoft SQL Server, Error: -1)
The error I use when connecting to the v7 machine is:
(provider: TCP Provider, error: 0 - A socket operation encountered a
dead network.) (Microsoft SQL Server, Error: 10050)
Any thoughts or ideas?
hi,
> The MSDE was installed without an instance name, so I believe the
> instance name is the name of the machine. I believe that I need to
> specify the machine and the instance name in the Server Management
> Studio connection dialog. I believe the format is
> MachineName\InstanceName
as long as you did not install a named instance, you only have to provide
the MachineName..
> In all cases I am using TCP/IP to connect.
obviously the relative network protocol has been enabled on the remote
server..
> All authentication is SQL Auth. The MSDE machine is configured to use
> SQL Auth. The MSSQL v7 machine is too.
> I was a bit surprised to see the requests on port 1434 instead of
> 1433. I'll learn about the reason(s) for this later. Right now, I
> want to know if there are any considerations regarding this.
TCP/IP 1433 is the IANA assigned port to Microsoft SQL Server, and is the
default port for default instances of SQL Server... named instances usually
adopt the dynamic allocation of a port, so another service is involved,
running on UDP 1434 port, which resolves the connection enquiry redirecting
to the appropriate named instace's used TCP port..
> There are firewalls involved, they have been configured to allow
> access. This is confirmed by being able to use the MSSQL v7 tools to
> connect to both the MSDE and the v7 servers.
> The error I receive when connecting to the MSDE machine is:
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance Specified) (Microsoft SQL Server, Error: -1)
> The error I use when connecting to the v7 machine is:
> (provider: TCP Provider, error: 0 - A socket operation encountered a
> dead network.) (Microsoft SQL Server, Error: 10050)
>
> Any thoughts or ideas?
try having a look at
http://support.microsoft.com/default...b;en-us;905618 ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, March 19, 2012
Connecting to MSSQL Server 2000 using JDBC
Hello,
I am developing an application which needs JDBC connection to a Named Instance of SQL Server 2000 (other than default instance). I am facing problem in this regard as my code gives an exception "Time Out" or "Connection Failed". However it works fine with the default named instance...The main driver class is "com.microsoft.sqlserver.jdbc.SQLServerDriver"
and Connection URL is
"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"
It gives the exception that Unable to connect to named instance....
Please if any one have any knowledge in this regard....do let me know....I have read an article on MSDN according to which on MS SQL Server 2000 the named Instance other than default can be accessed only through named pipes.
http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx
if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...
Thanks
Muhammad:
The JDBC driver does connect to named instances but doesn't use Named Pipes -- only TCP/IP. In your case it looks like you're overloading the instanceName property in the connection string by putting the instanceName and portNumber together. Try breaking them apart like this:
"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"
If that still doesn't work let me know.
-shelby
|||Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..
"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"
One thing i want to discuss with you is that when I go to "Server Network utility" the value written in default port for the second instance in "TCP/IP" is 0..why it is so? I have chaged it to 1432 myself and have restarted the server again....My code is as follows (however it is very simple but I am sending it)
import java.sql.*;
public class DatabaseTest
{
public static void main(String[] args)
{
try
{
java.lang.Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
// Class.forName("com.inet.tds.TdsDriver");
Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost;instanceName=TESTING;portNumber=1432;databaseName=testDB;" );
// Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost/pipe/MSSQL$TESTING/sql/query?database=testDB&user=sa&password=abc");
// Connection c = DriverManager.getConnection("jdbc:inetdae7://localhost/pipe/MSSQL$TESTING/sql/query?database=CMS&user=sa&password=abc");
Statement s = c.createStatement();
ResultSet r =s.executeQuery("select * from table1");
while(r.next())
{
System.out.println(r.getString(2));
}
System.out.println( "Connected!" );
}
catch( Exception ex )
{
ex.printStackTrace();
}
}
}
as you can see that in connection statement I have commented out some lines....that is connection string of some other driver I have downloaded for testing...and when I use it with piped names it connects perfectly to named instance...but I have to use the first driver....so please help me...
Thanks for your coordination
bye
|||At last i got the solution.....All the problem is due to cause that Service Pack 3.0 for SQL Server 2000 was not installed on my machine...as it is a bug in SQL Server 2000, that it does not listen on TCP/IP port, as u can verify by using TELNET, however this bug has been removed in SP3
Regards
Muhammad Ummar
Connecting to MSSQL Server 2000 using JDBC
Hello,
I am developing an application which needs JDBC connection to a Named Instance of SQL Server 2000 (other than default instance). I am facing problem in this regard as my code gives an exception "Time Out" or "Connection Failed". However it works fine with the default named instance...The main driver class is "com.microsoft.sqlserver.jdbc.SQLServerDriver"
and Connection URL is
"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"
It gives the exception that Unable to connect to named instance....
Please if any one have any knowledge in this regard....do let me know....I have read an article on MSDN according to which on MS SQL Server 2000 the named Instance other than default can be accessed only through named pipes.
http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx
if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...
Thanks
Muhammad:
The JDBC driver does connect to named instances but doesn't use Named Pipes -- only TCP/IP. In your case it looks like you're overloading the instanceName property in the connection string by putting the instanceName and portNumber together. Try breaking them apart like this:
"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"
If that still doesn't work let me know.
-shelby
|||Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..
"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"
One thing i want to discuss with you is that when I go to "Server Network utility" the value written in default port for the second instance in "TCP/IP" is 0..why it is so? I have chaged it to 1432 myself and have restarted the server again....My code is as follows (however it is very simple but I am sending it)
import java.sql.*;
public class DatabaseTest
{
public static void main(String[] args)
{
try
{
java.lang.Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
// Class.forName("com.inet.tds.TdsDriver");
Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost;instanceName=TESTING;portNumber=1432;databaseName=testDB;" );
// Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost/pipe/MSSQL$TESTING/sql/query?database=testDB&user=sa&password=abc");
// Connection c = DriverManager.getConnection("jdbc:inetdae7://localhost/pipe/MSSQL$TESTING/sql/query?database=CMS&user=sa&password=abc");
Statement s = c.createStatement();
ResultSet r =s.executeQuery("select * from table1");
while(r.next())
{
System.out.println(r.getString(2));
}
System.out.println( "Connected!" );
}
catch( Exception ex )
{
ex.printStackTrace();
}
}
}
as you can see that in connection statement I have commented out some lines....that is connection string of some other driver I have downloaded for testing...and when I use it with piped names it connects perfectly to named instance...but I have to use the first driver....so please help me...
Thanks for your coordination
bye
|||At last i got the solution.....All the problem is due to cause that Service Pack 3.0 for SQL Server 2000 was not installed on my machine...as it is a bug in SQL Server 2000, that it does not listen on TCP/IP port, as u can verify by using TELNET, however this bug has been removed in SP3
Regards
Muhammad Ummar
Connecting to MSSQL in VBA?
This is kind of the wrong place to ask VBA questions, but I've been poking around for a day and haven't got the answer, so I am hoping I can get some help here.
I am wondering if I can connect to MSSQL in MS Word Macro. If so, can I have some sample code please?
Thanks
here you go..look VBA section
http://www.programmingmsaccess.com/Samples/VBAProcs/vba_code_sample_menu.htm#VBA_Samples
|||That's a good VB resource, however, I didn't exactly find what I was looking for.
In Excel, I can open a direct connection to a sql server like this:
ActiveSheet.QueryTables.Add( Connection:="ODBC;DRIVER=SQL Server;SERVER=myserver;UID=myid;PWD=mypass;DATABASE=mydb", _
Destination:=Range("a1"), _
SQL:="mySP")
Theoretically, I can contain a spreadsheet (as intermedia database) in Word, but before heading that direction, I want to try to get a cleaner solution first.
From the code samples I've come across, appearently ADODB or ADO, or even ODBC can only point to database file like .mdb, but not MSSQL server.
I kind of ran out of keywords for Google, so I am hoping I can get some pointers here.
|||
The first thing is Excel since XP can connect to SQL Server and write queries the syntax is Access SQL so SQL Server even version 2000 rejects some queries, the second thing is there is a team writing the type of code you need that is the Visual Studio Office Tools team. I have found two links to get you started. Hope this helps.
http://msdn2.microsoft.com/en-us/library/aa168463(office.11).aspx
http://msdn2.microsoft.com/en-us/office/aa905533.aspx
Sunday, March 11, 2012
Connecting to Delphi
Thanks|||Hi,
Here is a simple way to connect to sql server using ODBC DSN & Delphi.
I hope this will help you.
1. Drop a Database Component from DataAccess Tab
2. Set the following properties for Database Component
1. AliasName - The DSN name created with ODBC
2. DatabaseName - The database you want to connect to sql server for example give "PUBS"
3. Login Prompt - False
4. Name - "PUBS_DB"
5. Params - USER NAME=SA
DATABASE NAME=PUBS
3. Now Drop a Table Component from DataAccess Tab
4. Set the following properties for Table Component
1. DatabaseName - PUBS
2. Now in "Table", select the table name you want
3. Active - True
5. Drop a DataSource and Link the dataset to Table
6. Now drop a DBGrid from DataControls and link the datasource.
Regards,
Selva Balaji B.|||i made it
thankz
i got another question, what about connecting to a server in my LAN, what should i configurate
thankz again
Thursday, March 8, 2012
Connecting to a web based MSSQL DB
Hi all,
I'm running a MSSQL DB on our web server and currently I need to use Teratrax Database Manager to connect and manage it. Unfortunately this doesn't seem to have any features to export the data from the database.
I read somewhere about an online MSSQL tool which can manage online databases? Does this exist?
What tool would I use to connect to my database to export data from it? I have an evaluation copy of MS SQL Server 2005 but my knowledge with it is limited and I'm having trouble getting it connected to a remote DB (local DBs are fine).
Any help would be appreciated!
Thanks!
Hi,
although there are some applications out there to administer SQL Server, they are just mimic to SQL Server Enterprise Manager / Managment Studio (you didn′t tell which version of SQL Server you want to administer). Perhpas you should get a CAL to administer the boxes on the web. SQL EM and SQLMS works fine over the internet and should save you some time compared to using a small featured similiar application.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Connecting to a SQL DB
I have a server with MSSQL Server installed there in which I've deployed my
DB.
I've also deployed an app (programmed with C# in .net) which access that DB.
Is there any chance to access that DB without installing the SQL Client?
It is very urgent.
Many thx.
SqlClient is part of the .NET Framework -- there is no separate
install and you can't uninstall the Framework when you're using C#.
However, I'm curious as to why you have a compelling need to uninstall
it.
--Mary
On Fri, 31 Mar 2006 00:56:02 -0800, Oscar
<Oscar@.discussions.microsoft.com> wrote:
>Hi,
>I have a server with MSSQL Server installed there in which I've deployed my
>DB.
>I've also deployed an app (programmed with C# in .net) which access that DB.
>Is there any chance to access that DB without installing the SQL Client?
>It is very urgent.
>Many thx.
Wednesday, March 7, 2012
Connecting to a linked server with ODBC
linked the Cache server through an ODBC connection. I can see in the Linked
Server
expansion all the tables in Cache for the File(?) I want to access.
Here is my problem: Normally to access a linked sever I would do the
following:
select *
from ServerName.DatabaseName.dbo.TableName
I have tried
select *
from ServerName.DSN.dbo.TableName
select *
from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
None have worked (error no such object...)
What is the syntax to select data on a linked sever via an ODBC connection?
In the Linked server set up I have also check the RPC and RPC Out options...
My ultimate goal is join tables in Cache and MSSQL into MSSQL.
Any help would be greatly appreciated!
Thanks,
-p"Pippen" <123@.hotmail.com> wrote in message news:<b8iWb.2520$_44.6454@.attbi_s52>...
> I'm currently trying to access data from a Cache DB using MSSQL. I have
> linked the Cache server through an ODBC connection. I can see in the Linked
> Server
> expansion all the tables in Cache for the File(?) I want to access.
> Here is my problem: Normally to access a linked sever I would do the
> following:
> select *
> from ServerName.DatabaseName.dbo.TableName
> I have tried
> select *
> from ServerName.DSN.dbo.TableName
> select *
> from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
> None have worked (error no such object...)
> What is the syntax to select data on a linked sever via an ODBC connection?
> In the Linked server set up I have also check the RPC and RPC Out options...
> My ultimate goal is join tables in Cache and MSSQL into MSSQL.
> Any help would be greatly appreciated!
> Thanks,
> -p
I don't know anything about Cache DB, but not all linked servers
support or require all 4 parts of the name. Have you tried these two
alternatives?
select * from LinkedServerName..SQLUser.TableName
select * from LinkedServerName...TableName
Simon|||Your first example works. Thanks for the help.
-p
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402110305.707515bb@.posting.google.c om...
> "Pippen" <123@.hotmail.com> wrote in message
news:<b8iWb.2520$_44.6454@.attbi_s52>...
> > I'm currently trying to access data from a Cache DB using MSSQL. I have
> > linked the Cache server through an ODBC connection. I can see in the
Linked
> > Server
> > expansion all the tables in Cache for the File(?) I want to access.
> > Here is my problem: Normally to access a linked sever I would do the
> > following:
> > select *
> > from ServerName.DatabaseName.dbo.TableName
> > I have tried
> > select *
> > from ServerName.DSN.dbo.TableName
> > select *
> > from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)
> > None have worked (error no such object...)
> > What is the syntax to select data on a linked sever via an ODBC
connection?
> > In the Linked server set up I have also check the RPC and RPC Out
options...
> > My ultimate goal is join tables in Cache and MSSQL into MSSQL.
> > Any help would be greatly appreciated!
> > Thanks,
> > -p
> I don't know anything about Cache DB, but not all linked servers
> support or require all 4 parts of the name. Have you tried these two
> alternatives?
> select * from LinkedServerName..SQLUser.TableName
> select * from LinkedServerName...TableName
> Simon
Friday, February 24, 2012
connecting mssql to access and oracle
A good example would be fine
I would like to use ODBC or any free tools
THANKS
SEJCreate a linked server or use openquery/openrowset.
Access shold be easy as you should already have the driver but will need to install the oracle connectivity layer for oracle.|||Originally posted by nigelrivett
Create a linked server or use openquery/openrowset.
Access shold be easy as you should already have the driver but will need to install the oracle connectivity layer for oracle.
Can you give some details
I have sql developer 2000 and oracle9i and access on the same machaine with 2000 professional
Novice i am in connectivity issues
with example will help
SJ
connecting MSSQl Server in local IP
We are hosting a Database Server for our client server desktop application
The Database server(Microsoft SQL Server 2000) is in the internet with a Public IP as of now
Now we have planned to bring our SQL Server to Local IP i.e. behind firewall for security reasons
How this can be done? What should I do?
What are all the Possiblity solution?
What is the server name or IP should i give when a when my client application connects to the Database server.
How the connection can be established
anyone kindly tell me the possible methods so that my clients must be able to access the database server which is in local IP
Thanks and regards
Sabeerin "server network utility" enable Named Pipes protocol...you should be able it access it from your local network, or application using the database server name
(find this by running select @.@.servername on the server)
If you want it to still be accesible on the outside, leave TCP/IP enabled with ext ip in properties|||yes I understand
The Thing is My SQL Server is hosted by our Service Provider
Now What should I ask My Service Provider to do to bring it to local IP
as so far I was using Public IP.
So what should be done
So that my client application is able to to access the database server which is in Local network in the service Provider
Can you guide me further
Regards
Sabeer|||you need to ask your provider to set up a NAT (network address translation) for you. basically you will be asking them to forward any requests made on a certain port (1433 for sql default) on the external IP to the relevant port on your internal IP ...
client apps will still use external IP for connections...
cheers
des|||Hi
Thanks
I shall try it out with my Service Provider
Regards
Sabeer
Sunday, February 19, 2012
connecting DB on a pendrive in MSSQL 7.0
Hoping for you HELP
THank YOu
Vikram JainCheck out sp_attach_db and sp_detach_db in the MS-SQL 7.0 Books Online.
-PatP|||SP_Attach_DB ?? How can this procedure be made run through VB Code.
If you can send me a sample code it would be highly appreciated..
Thanks Regards Vikram Jain
Friday, February 17, 2012
Connecting an ADP to a remote MSSQL database on the same network
When I go into MS Access, on another PC (running Windows XP Pro) on the same network, and try to create a using 'Project(Existing Data)' and connect it to MSSQL 2000 on my desktop, I fill in the Data Link Properties '1. Select or enter server name', no problem, '2. I click on Use Windows NT integrated security ',no problem. On '3. Select the database on the server', I get the following message -
SQL Error [DBNETLIB]{ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
I've been to more websites then I can count to try and resolve this error to no avail. Could someone give me a simple solutions, or at least some direction on this.
Thanks in advance for any help you can provide.
BurtTry to create DSN instead and use it instead of Server name.
Good Luck