Re: Failed to connect to SQL express on LAN


Charles
Although I wrote that SQL Authentication worked with the sa account, once
connected I have no functionality for that server. There is no option to
Start/Stop or attach/detach datbases. I tried another account that I had
made "dbadmin thand that failed to login with error" Login failed for user
'dbadmin', (error: 4064).

I went on the DELL8300 server and tried to enable nore server roles for the
sa account but and I got an error msg : "canot use the special principal sa,
error: 15405.

So I am back to square one with no remote managment over the LAN. Should
this work?
All I want to do is use the SSMS over the LAn to manage SQL server on
another machine on the same LAN.

Thanks
Morris


""Charles Wang [MSFT]"" wrote:

Dear Morris,
Welcome to Microsoft MSDN Managed Newsgroup.

I am not sure if there is any restriction on your DELL computers, however
normally if two computers are in a workgroup environment, the Windows
Authentication between computers is NTLM. In this case, create a same local
user account on both the two computers should be helpful. Here are the
steps for your reference:
1. On your DELL8300 computer, right click "My Computer", click Manage, go
to Users and Groups, create a new user "testadmin" with the password
"MyPwd01!" and add it to the Administrators group;
2. On your DELL4600c computer, follow the step 1 to create the same user
account testadmin with the same password;
3. On your DELL8300 computer, go to Services panel and make sure that SQL
Server Browser services is started;
4. On your DELL8300 computer, run firewall.cpl from command line and make
sure that your SQL Server Express program has been added to the Exception
list;
5. On your DELL8300 computer, click Start->All Programs->Microsoft SQL
Server 2005-> SQL Server Surface Area Configuration, click Surface Area
Configuration for Services and Connections, select SQLEXPRESS\Database
Engine\Remote Connectionis, and make sure that "local and remote
connections" and "Using both TCP/IP and named pipes" are checked and
restart your SQL Server Express instance;
6. On your DELL4600c computer, click Start->All Programs->Microsoft SQL
Server 2005, right click SQL Server Management Studio, click Run as...,
input the testadmin user account and password to run the SSMS;
7. Once the SSMS is started by the testadmin user account, input the
instance name DELL8300\SQLEXPRESS, use Windows authentication to connect to
the instance.

For SQL Authentication, from your test, I think that SQL Authentication was
disabled on your SQL Server Express instance, so in this case, you could
not use a SQL login to log on your SQL Server instance.
If you have SQL Server 2005 Management Studio Express (SSMSE) installed on
your computer DELL8300, you can refer to this article to enable both
Windows and SQL authentication:
How to: Change Server Authentication Mode
http://msdn.microsoft.com/en-us/library/ms188670(SQL.90).aspx

If you do not have SSMSE installed, I recommend that you download it from
this link and install it on your DELL8300 computer:
http://www.microsoft.com/downloadS/details.aspx?familyid=C243A5AE-4BD1-4E3D-
94B8-5A0F62BF7796&displaylang=en

If you do not want to install SSMSE on your computer, you can try the
following steps to enable SQL login:
1. Run the sqlcmd command from command prompt with a local windows
administrator:
sqlcmd -S .\SQLEXPRESS -E

2. Input the following command to enable SQL and Windows Authentication and
enable sa login:
1> EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
2> Go
1> ALTER LOGIN sa ENABLE;
2> Go
1> ALTER LOGIN sa WITH PASSWORD='MyPwd01!'
2> Go

3. Open SQL Server Configuration Manager or Services panel to restart your
SQL Server services

4. Then you can login your SQL Server Express instance with the SQL login
sa.

Hope this helps. Please do not hesitate to let me know if you have any
other questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================



.

Relevant Pages

  • Re: SQL account rights
    ... Please advice what is the best, suitable rights rather than domain admin ... issues, such as a server that might have IIS running on the same machine, ... applicable to SQL 2000 environment, ... files, or backups, make sure that the service account has Full ...
    (microsoft.public.sqlserver.security)
  • RE: MP Install issue
    ... Where in the installation are you talking about specifying the account rather ... > MPDB ERROR - CONNECTION PARAMETERS ... > SQL Server Name: servername ... > with a trusted SQL Server connection. ...
    (microsoft.public.sms.setup)
  • Re: SQL Express Fails with Hardware Error
    ... The LocalSystem account is a built-in account, ... which the SQL Service runs. ... MCSE, CCEA, Microsoft MVP - Terminal Server ... Minimum Hardware Requirement (Warning) ...
    (microsoft.public.sqlserver.setup)
  • Re: SCCM with a remote SQL instance problems (IT IS NOT A WARNING)
    ... PreReq check is not a WARNING it is a FAILURE. ... account the run the SQL Server Service on the server, Domain Memberships, AD ...
    (microsoft.public.sms.installer)
  • Re: Distributed Query - Linked Servers?
    ... In the case of SQL style accounts, it doesn't seem very secure to me, because the password will be transmitted over the network. ... it should also be able to work by setting up a credential and proxy account on Server1. ... In this case it could work with NTLM authentication. ... we just need to verify that the 2 sql server instances are under sql server and windows authentication mode and the sql account you use has the permission on the 2 sql server instances. ...
    (microsoft.public.sqlserver.server)