MSDE 2000 SP3a Workgroup Access

Greetings everyone.

I work in a retail environment and have an MSDE2000 SP3a server installed in 350+ stores. I need to access the server from another computer in the workgroup (not domain) and I can't. I'm getting "SQL Server does not exist or access denied." The server was installed using the default option of Windows Authentication and I have changed it to Mixed mode authentication on one of my in-house lab systems with no luck. Also, when I try to create and OLEDB connection for the database and drop down the list of servers, there is nothing there (not even the local server.) Sorry but I do not have the error message that I get when that happens but I think when I solve the first problem the second will go away too.

Thanks for your inputHi,

Are you saying that when you changed a server to mixed mode you were still unable to connect to it? Even from the local machine?

In my experience with MSDE, when running in mixed mode you should be able to connect remotely with EM or QA with the UID & PWD.

Have you confirmed that you are able to connect over 1433/1434 on the network? I guess this is the equivalent of "have you checked your cables" but it's worth checking to make sure.

Dan|||Hi Dan -- thanks for the reply.

I have always been able to connect to the server, both in Windows Authentication mode and Mixed mode from the local machine on which the server resides. The problem is that I cannot connect in either mode from a different machine in the workgroup. I tried telnet to the local machine using port 1433 and got a connect failed even on the same machine as the server. I did not know about port 1434. I'll try that one when I get back to the office tomorrow.

Thanks Again|||Use the server network utility.|||Not to be dense but what's the "Server Network Utility?"

I know about osql and can connect to the server while on the local machine but not from another machine.|||It's one of the utilities installed with SQL. You'll need to run it from the machine you want to connect from. You'll need to set up the connection. You create an alias where you specify the connection properties for the server.

Dan|||Nevermind -- I found it. I dialed into my office computer and ran the server network utility and all protocols were disabled. I won't be able to check the results until Friday but I'm optimistic that this will resolve my problems. Thanks a lot.|||all protocols were disabled That will do it.|||Once I enabled named pipes and tcp/ip on the server, the workgroup connection works fabulously. Thanks for your help.

One more question. Is there a registry setting for this that I can download a .reg file to the stores to set this or do I have to manually configure this setting in all my stores? Thanks again.

MSDE 2000 database setup trouble

Hello
I am running a stand-alone test environment for an application in XP Pro
which I can only use MSDE to manage my databases.
I've successfully installed msde and am finally able to connect using 'osql
-Usa -Ppassword -S (local)\testmachine' , because I can get the '1>' command
prompt.
My trouble is when I try to create and/or restore a database for the test
application to use I'm running into trouble. For example enter this :
1> USE master
2> RESTORE DATABASE master FROM DISK='C:\master\master_db2.BAK'
3>GO
and I get an error message telling me that "RESTORE DATABASE must be used in
single user mode when trying to restore the master database" ...... I also
get a similar message when trying to create and restore a database called
'public'.
how do I successfully get into 'single user mode' ?
hi,
APB wrote:
> Hello
> I am running a stand-alone test environment for an application in XP
> Pro which I can only use MSDE to manage my databases.
> I've successfully installed msde and am finally able to connect using
> 'osql -Usa -Ppassword -S (local)\testmachine' , because I can get the
> '1>' command prompt.
> My trouble is when I try to create and/or restore a database for the
> test application to use I'm running into trouble. For example enter
> this :
> 1> USE master
> 2> RESTORE DATABASE master FROM DISK='C:\master\master_db2.BAK'
> 3>GO
> and I get an error message telling me that "RESTORE DATABASE must be
> used in single user mode when trying to restore the master database"
> ...... I also get a similar message when trying to create and
> restore a database called 'public'.
> how do I successfully get into 'single user mode' ?
have a look at http://msdn2.microsoft.com/en-us/library/ms180965.aspx ..
typically you open a command window, navigate to the \Binn folder of the
instance you want to start and execute something like
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\>sqlservr.exe -s
InstanceName -m
this will start the instance in single user modo, so that you'll be able to
restore the master database...
to restore "normal user's" database(s) the single user mode is not required,
but the database(s) to be restored must not be in use... so no active
connections must be running against the database(s) to be restored..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
-- remove DMO to reply