Getting to the bottom of MSDE network connection problems ...
Nicky Smith
Hi All,
I've read A LOT of posts concerning connection problems with the
various versions of MSDE and Windows XP
I'm using the one packaged as MSDE2000a.exe which has a new EULA text
but other than that it is MSDE 2000 with sp3a already applied.
I've finally figured out the connection problems associated with this,
and it seems the problems are due to Windows XP and not MSDE. A lot of
people posting replies, trying to help, are making assumptions based
on their own experience, as did I. In a perfect world MSDE should
install and opperate just as easily as SQL Standard or Enterprise, but
it doesn't! mainly because of the OS differences.
For one, the enterprise/standard versions of SQL server won't install
on a desktop OS like XP (meaning that, you can not compare SQL Server
and MSDE on an XP machine) and so, developers and such turn to MSDE.
Because everything goes well with local installations and connections,
they believe that there is no difference. Also, I like many others,
have easily installed MSDE on Windows 2000 server, and haven't
experienced the problems with connectivity and so I believed that
there was no difference. So why all of the problems connecting to MSDE
on an XP machine?
Heres what I did:
notebook pc with Windows XP sp2 + all latest updates applied.
MSDE2000a.exe installed and running with no problems.
This is the contents of the setup.ini file I used:
[Options]
TARGETDIR=c:\msde2000a\binn
DATADIR=c:\msde2000a\Data
BLANKSAPWD=1
DISABLENETWORKPROTOCOLS=0
I then ran setup.exe with the /Settings switch and the FULL PATH to
the setup.ini file mentioned above.
before you complain about the blank password switch, don't - it was
only used during installation on a secure lan.
After installation I installed the management-tools-only from the
Developer Edition CD so I would have a local copy of Enterprise
manager/query anylizer available.
After a restart, I opened up the Enterprise Manager and right clicked
on the local server, and selected Properties.
In the props dialog box, I clicked on the General Tab, and down at the
bottom, selected the large button 'network configuation'. In this SQL
Network utility, I removed named pipes and only left TCP/IP in the
Enabled protocols list, and clicked on properties (of TCP) to ensure
that the protocol was using the standard port 1433, clicked OK a
couple of times, then used the service manager to stop and restart the
server service and the Agent service.
I then use EM again and opened the local sql server properties dialog
again and selected the security tab. I made sure that the server was
set to Windows Only for authentication mode, and closed that dialog.
After closing that dialog, I expanded the server in the EM and clicked
on the Security Node and then double-clicked on the SA account in the
details view on the right side. I then set a long secure password for
the SA account. (even though the server is now using windows-only).
Many posts have indictaed that using SQL integrated security on XP
(instead of windows only), did work, but users would have to supply
their credentials. And this is not the best way to run a server when
Windows Authentication is available. Also, those delevoping network
client apps for SQL Server would have to use integrated security and
embed a clear text version of a user's password and login for the
connection.
Testing the SQL connection from a remote machine:
In Windows-Only mode, the first problem was a connection failiure from
another XP machine, even though both logins were identical. Example:
Notebook SQL Server: login: nick - pass: gfhhfhsds12
PC on same Lan: login: nick - pass: gfhhfhsds12
The error message was "Login failed for user (Null)". At first, i
couldn't understand this and took a break. Later I needed to copy some
files to the notebook running sql server and so I shared a folder.
This apparently Enabled the Guest account on the sql server machine
(the windows xp local guest account).
When I tried to connect to the sql server again, this time the error
message read: "Login failed for user xpcompaq\guest".
So I figured the login process was somehow translating my login name
'nick' to the guest account, and because there is nothing enabled in
SQL server for a login called 'guest' it did of course fail! And, the
user name came up as Null the first time around because there was no
active account on the XP/SQL machine called 'guest'.
So why does it do that?
Well ... here it is: Windows XP has a security policy enabled (at
least it does with sp2 installed) that translates all network
connections to the guest account! So it doesn't matter if you have
identical logins and passwords, you're still going to login as a
guest!
How to fix:
The group policy editor for Windows XP (and here I'm only talking
about the Pro edition, I have never used Home edition), is hidden and
doesn't appear in the Administrative tools folder.
You have to click on start, run and type: gpedit.msc and then OK.
This will start the group policy editor console.
now expand the Computer Configuartion icon, and then expand Security
Settings. next, expand Local Policies, then click on Security Option.
This will fill the right side of the gp editor with a long list of
security policies.
Scroll down this list and find all of those that start with 'Network
Access:'
You're looking for a policy called: "Network Access: Sharing and
security model for local accounts". Double click on it.
You'll see that it is set to: 'Guest Only - local users authenticate
as Guest' .... the Bastard!!! This was the cause of the troubles!!
Change this to: Classic - local users authenticate as themselves.
This means that when nick on a lan pc connects to the xp machine he
will connect as nick, not guest. (as long as the passwords mach as
well).
now wait, we're not finished yet ...
If your'e using a software firewall on the XP machine, then open 2
ports for incoming: TCP port 1433 and UPD port 1434
You will need to restart the computer after making the Policy changes.
The firewall changes should be active straight away.
If you still have problems connecting there is one more security
policy you might need to check:
Using policy editor again, locate this node:
Computer Configuration - Windows Settings - Security Settings - Local
Policies - User Rights Assignment.
Then from the list of policies on the right, double click on "Access
This Computer from the Network"
You need to make sure that your login, or at least a group you're a
member of is in this list.
If you're on a Windows Domain, and you can't make these changes, then
I suppose you're up shit-creek without a paddle!
Well, I hope this rather long explanation helps people with connection
problems. And if anyone from MS is reading, please put this in the KB
or at least post it on the MSDE download page. These connection
problems have been a pain in the arse and the information available
does not help.
_nick
Norman Yuan
authenticated as GUEST in WinXP SP2. Most deveploers simply do not realize
this XP SP2 security change hidden deeply somewhere, especially in a small
peer-to-peer network. It should be listed to an MSDE FAQ, if there is a
place of FAQ.
"Nicky Smith" <nxspam-01[at]yahoo.com> wrote in message
news:0d2261pbth7dkcrtu...@4ax.com...
Rob S
Your comments were very helpful -- figures MS would throw some tricks at us.
I have a question about a similar problem. I have installed MSDE (through
an Installshield package) on a domain client using a domain admin account.
Everything works fine. When I re-boot and login as a domain user, my
application won't connect to the DB. I have tried adding the domain user to
the Admin group (just to testing purposes), but this does not fix the
problem. Do you have any suggestions or ideas why this problem occurs? By
the way using the same installation scenario on Win2K works with no problem.
Thanks, Rob