Connecting to remote SQL Server

I have SQL Server 2000 (Developer) on my computer. So I have SQL
Server Enterprise Manager + Tools (such as Query Analyzer).
I am doing some work on a remote server, mostly ASP pages (not
ASP.NET). There is a SQL Server database on that server. I would like
to use Enterprise Manager or Query Analyzer to access that database.
My main development environment is Dreamweaver MX2004. In Dreamweaver,
I was able to setup the database (on the databases tab) using a
"Custom Connection String", listed below (some info obfuscated for
security):
"Provider=SQLOLEDB.1;Persist Security Info=False;Initial
Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
I don't know if it is possible to use the other tools to manage this
database. I've tried the following:
1. In Enterprise Manager, I've tried just putting the ip address as
the server, and "Use SQL Server authentication" from the user ID and
Password above, but I get the following error:
"SQL Server registration failed because of the connection failure
below... Cannot open user default database. Login failed"
2. Created a udl file. In the Provider, selected the "Microsoft OLE DB
Provider for SQL Server" Entered teh IP address and username/password
on the Connection Tab. I get the same error as #1 above.
Any insight would be greatly appreciated."Bryce Fischer" <spamtrap@.berzerker-soft.com> wrote in message
news:3btpo01us397pf1gmfl4bt9c8k9dcrv05e@.
4ax.com...
> I have SQL Server 2000 (Developer) on my computer. So I have SQL
> Server Enterprise Manager + Tools (such as Query Analyzer).
> I am doing some work on a remote server, mostly ASP pages (not
> ASP.NET). There is a SQL Server database on that server. I would like
> to use Enterprise Manager or Query Analyzer to access that database.
> My main development environment is Dreamweaver MX2004. In Dreamweaver,
> I was able to setup the database (on the databases tab) using a
> "Custom Connection String", listed below (some info obfuscated for
> security):
> "Provider=SQLOLEDB.1;Persist Security Info=False;Initial
> Catalog=funeral;Data Source=xx.xx.xx.xxx;Locale
> Identifier=1033;Connect Timeout=15;Use Procedure for Prepare=1;Auto
> Translate=True;Packet Size=4096;User Id=XXX;PASSWORD=XXXXXXXX;"
> I don't know if it is possible to use the other tools to manage this
> database. I've tried the following:
> 1. In Enterprise Manager, I've tried just putting the ip address as
> the server, and "Use SQL Server authentication" from the user ID and
> Password above, but I get the following error:
> "SQL Server registration failed because of the connection failure
> below... Cannot open user default database. Login failed"
>
User XXX has a login on the remote server, but does not have a user in the
database he is configured to use. To administer the remote database, User
XXX must be a member of the sysadmin role on the remote server
http://snodland.blogspot.com
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004|||On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
<bob_simms@.somewhere.com> wrote:

>User XXX has a login on the remote server, but does not have a user in the
>database he is configured to use. To administer the remote database, User
>XXX must be a member of the sysadmin role on the remote server
Thanks for the reply.
Is it possible to use Query Analyzer or Access to connect to the
server given the information in my original post? I don't need
administrator-like functionality. As I mentioned, in Dreamweaver, I'm
able to create a connection, and view the tables and data (not to be
confused with creating the connection on the ASP pages, but in
Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
it doesn't let you run SQL Statements, which is why I'd like to use
other tools I have.
TIA.|||On Sat, 06 Nov 2004 20:21:58 -0500, Bryce Fischer
<spamtrap@.berzerker-soft.com> wrote:

>On Sat, 6 Nov 2004 22:04:54 -0000, "Bob Simms"
><bob_simms@.somewhere.com> wrote:
>
>Thanks for the reply.
>Is it possible to use Query Analyzer or Access to connect to the
>server given the information in my original post? I don't need
>administrator-like functionality. As I mentioned, in Dreamweaver, I'm
>able to create a connection, and view the tables and data (not to be
>confused with creating the connection on the ASP pages, but in
>Dreamweaver itself.) Dreamweaver lets you browse the tables, and data,
>it doesn't let you run SQL Statements, which is why I'd like to use
>other tools I have.
Well... After some persistence, I figured it out. In Access, and
Visual Studio, I was able to bring up the Data Link Properties dialog,
and enter the information, and the one step I was not doing, was
entering in the catalog in the "Select the database on the server:"
field.
I was able to accomplish the same thing in Visual Studio, Server
Explorer, Data Connections.