[thelist] how to connect to MSDE SQL Server?

Ken Schaefer ken at adOpenStatic.com
Wed Apr 14 21:15:20 CDT 2004


Of course you can connect to MSDE across the network. :-)

a) That said, you need to specify what network library you are trying to
use. For example, SQL Server/MSDE allows TCP/IP by default, however if you
have some kind of firewall on the server that is blocking the ports SQL
Server/MSDE uses (1434/1433), then you're not going to be able connect
across the network using TCP/IP. Similar for the other protocols (named
pipes etc)

b) SQL Server has two authentication modes. Integrated Windows, and Mixed
Mode (Integrated Windows + SQL Server). Microsoft is pushing Integrated
Windows Authentication, rather than Mixed Mode (which will soon become a
legacy feature). With IW Auth, you need to:
    - grant the appropriate windows account permissions to login to the
database
    - give the login appropriate rights to the databases in question
    - connect to SQL Server using that Windows context

This trips up a lot of people. Whatever your other programs are, you need to
connect to MSDE/SQL Server using the appropriate Windows user context -or-
switch SQL Server over to Mixed Mode, and use the "user
id=...;password=...'" in your connection string.

In troubleshooting this, I would suggest that:
a) you post the actual error message you are getting
b) download some kind of management tools (rather than having to use the
command line osql.exe). Microsoft has a web-based one here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
or if you're rather avoid web-based tools, you can download the trial
version of SQL Server, and just install the client tools (like Enterprise
Manager etc), so we can get quick access to the necessary config
information.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Chris Johnston" <chris at fuzzylizard.com>
Subject: [thelist] how to connect to MSDE SQL Server?


: I have downloaded and installed the Microsoft MSDE sql server. I have it
: installed on my computer and can access the server and create databases
: and tables using Visual Studio .NET 2003. However, if I try to access
: the server using any other method -- ODBC, Aqua Data Studio -- I keep
: getting a connection refused.
:
: The point of downloading MSDE is to hook it up to ColdFusion for
: development purposes. However, I need to figure out how to connect to
: the server in order to do that.
:
: A few details:
: This is what I downloaded: MSDE 2000 for Developers using visual studio
: .net - sql2kdesksp3.exe
:
: This is the command that I used to set it up with:
: Setup.exe /qb+ INSTANCENAME=VSDOTNET DISABLENETWORKPROTOCOLS=0 SAPWD=a
: password
:
: This should have enabled the server to be accessed over a network,
correct?
:
: So, the question is; how do I connect to this thing with something other
: than Visual Studio .NET? Is it even possible to use MSDE for something
: other than Visual Studio .NET development?
:
: Thanks
: chris



More information about the thelist mailing list