Troubleshoot connecting client to SQL Server instance on a server

[John Hackert]

I am in the process of testing a SQL Server Express 2005-Access 2007
ADP initially created on a single-user desktop in a multiuser
environment, but cannot connect a test client.

This is the specific problem:
When I open the ADP file on the client, select "Server" from the menu
then "Connection," under "Select or enter a server name," the instance
I wish to connect to appears, as "SERVER\SQLEXPRESS"

After selecting this, leaving the default "Use Windows NT Integrated
security" option, when I go to "Select the database on the server,"
the following error occurs:

Title: Microsoft Data Link Error
Message: "Login failed for user ". The user is not associated with a
trusted SQL Server connection."

The server is simply a PC with an evaluation copy of Windows SBS R2
installed on a partition. The client (running XP SP2) is
successfully connected to the server itself. I believe I have
configured SQL Server Express on the server to accept client
connections. Specifically, via Surface Area Configuration, under
"Database Engine," the "Remote Connections" options are "Local and
remote connections" and "Using TCP/ICP only." The "SQL Server
Browser" the startup type is "Automatic" and the service status is
"Running"

In SQL Server Management Studio Express, the database is properly
attached. One problem I suspect may be causing or contributing to the
overall connection problem, however, is that I cannot seem to
specifically add a client user. Under the database in question, when
I select "Security" then "Users" and right-click on Users to select
"New User," I have not seemed to be able to enter a recognized "Login
name." If I click on the ellipsis (3 dots) and try "Browse" to look
for a matching logon, I only see entries relating to the server, but
none of the two test clients.

I have not been able to figure out the problem from previous posts,
Books On Line, or the books I have on hand.

Any insight would be so appreciated.

 

[Sylvain Lafontaine]

Many possibilities here. For exemple, ADP doesn't use TCP but named pipes
or the windows account that you are using with ADP has not been set up for
any database on .\SQLEXPRESS.

Your post is very confusing because you don't say if you are using the same
account with ADP than with SSMSE and if the server is located on the same
machine or if SERVER is an alias that you have created.

From your description, I believe that your two test clients are not part of
a trusted domain for the server; so you cannot use a windows account with
them. Try with a SQL account instead and make sure that you are using TCP
and not a named pipe by creating an alias or using the prefix tcp: . Here a
some more info to help you with your login problem:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm (connection strings for
sql providers)

 

[Norman Yuan]

From the error you get, it is obviuosly, the Windows user account of the
computer where you log on and run your ADP, cannot be authenticated the the
server computer where the SQL Server Express is running. It also seems that
the SQL Server Express is set up correctly and accessible via the network,
except its security, namely, its SQL Login.

Are you on a client/server domain network, or it is a peer-to-peer network?

If it is a client/server domain, yuo need to log on your computer (where ADP
runs) with a domain user account, and in the SQL Server, you map a SQL
Server login to that domain user account (or a domain security group, in
which your domain user account is a member).

If it is a peer-to-peer network, you can only log onto your computer with a
local account, and natually, the other computer (the SQL Server computer)
does not know user accounts on other computer. To work around this, you need
to set up a user account on the SQL Server computer and map it to a SQL
Server login, and then, you create a user account on your computer with the
exactly the same user name and password. Then you log onto your computer
with this account, and then try to connect to the SQL Server with Windows
security. The other work around for peer-to-peer network is to use SQL
Server security, instead of Windows security. That is, enable the SQL
Server's mixed security mode, connect to it by supplying login's
username/password.

 

[John Hackert]

From the error you get, it is obviuosly, the Windows user account of the
computer where you log on and run your ADP, cannot be authenticated the the
server computer where the SQL Server Express is running. It also seems that
the SQL Server Express is set up correctly and accessible via the network,
except its security, namely, its SQL Login.

Are you on a client/server domain network, or it is a peer-to-peer network?

If it is a client/server domain, yuo need to log on your computer (where ADP
runs) with a domain user account, and in the SQL Server, you map a SQL
Server login to that domain user account (or a domain security group, in
which your domain user account is a member).

If it is a peer-to-peer network, you can only log onto your computer with a
local account, and natually, the other computer (the SQL Server computer)
does not know user accounts on other computer. To work around this, you need
to set up a user account on the SQL Server computer and map it to a SQL
Server login, and then, you create a user account on your computer with the
exactly the same user name and password. Then you log onto your computer
with this account, and then try to connect to the SQL Server with Windows
security. The other work around for peer-to-peer network is to use SQL
Server security, instead of Windows security. That is, enable the SQL
Server's mixed security mode, connect to it by supplying login's
username/password.













- Show quoted text -

Thank you for the responses

In the usual manner, when it comes to "advanced" topics in Microsoft
products, I envision a simple concept which nonetheless proves to be
technically complicated or impossible to execute.

Really another way to ask the question is this - how does one
successfully and securely configure the following elements:

- A server running Windows Small Business Server
- No more than 5 client computers
- A frontend/backend scheme using SQL Server Express and Access (via
an ADP)


I believe I have the Windows SBS client logons themselves working
properly.


Does anyone actually use SQL Server Express with Windows
Authentication on a client/server domain-based network?

 

[Norman Yuan]

Thank you for the responses

In the usual manner, when it comes to "advanced" topics in Microsoft
products, I envision a simple concept which nonetheless proves to be
technically complicated or impossible to execute.

Yes, using SQL Server/Express should be an "advanced" topic. We just see too
many similar questions are post daily in this NG or other related NG on how
to connect an application to SQL Server/Express.

Sql Server\Express is a sophisticated server product, one needs to learn
some basics before jumping on: installing and connecting. It does not work
any more, as it used to. Before, in the MS Windows world, everything is
open, until you explicitly close it. Nowadays, everything should be closed
by default, unless you explicitly opens it. In SQL Server, for example, the
mixed security mode is not enabled, you must specify password for "sa"
account, remote access is not enabled...

Popping CD into drive and following installation wizard no longer guarantees
an advanced product, such as SQL Server, works as you expected.

Really another way to ask the question is this - how does one
successfully and securely configure the following elements:

- A server running Windows Small Business Server
- No more than 5 client computers
- A frontend/backend scheme using SQL Server Express and Access (via
an ADP)

There is really not as difficult as you imagine with due study and
preparation. Take SQL Server Express as an example. You really need to do is
after installing it, use the "Surface Area Configuration" tool to configure
the server to make sure only requred services are running; and then set up
SQL Server security (Authentication mode and logins). You need to to a bit
planning.

In the scenario you mentioned above, whether you have no more than 5 client
computer or you have 20, 30, it does not matter. It is obviously better to
use Windows security mode for the SQL Server/Express. However, you can alway
enable mixed security mode to use SQL Server security, if there is reason
for it.

I believe I have the Windows SBS client logons themselves working
properly.


Does anyone actually use SQL Server Express with Windows
Authentication on a client/server domain-based network?

I have been using MSDE (since its 1.0)/SQL Server Express in a office with
30 users for long time. Windows Authentication or SQL Server Authentication
itself isn't the problem, the problem is to know/learn to use it.

 

[John Hackert]

Yes, using SQL Server/Express should be an "advanced" topic. We just see too
many similar questions are post daily in this NG or other related NG on how
to connect an application to SQL Server/Express.

Sql Server\Express is a sophisticated server product, one needs to learn
some basics before jumping on: installing and connecting. It does not work
any more, as it used to. Before, in the MS Windows world, everything is
open, until you explicitly close it. Nowadays, everything should be closed
by default, unless you explicitly opens it. In SQL Server, for example, the
mixed security mode is not enabled, you must specify password for "sa"
account, remote access is not enabled...

Popping CD into drive and following installation wizard no longer guarantees
an advanced product, such as SQL Server, works as you expected.





There is really not as difficult as you imagine with due study and
preparation. Take SQL Server Express as an example. You really need to do is
after installing it, use the "Surface Area Configuration" tool to configure
the server to make sure only requred services are running; and then set up
SQL Server security (Authentication mode and logins). You need to to a bit
planning.

In the scenario you mentioned above, whether you have no more than 5 client
computer or you have 20, 30, it does not matter. It is obviously better to
use Windows security mode for the SQL Server/Express. However, you can alway
enable mixed security mode to use SQL Server security, if there is reason
for it.





I have been using MSDE (since its 1.0)/SQL Server Express in a office with
30 users for long time. Windows Authentication or SQL Server Authentication
itself isn't the problem, the problem is to know/learn to use it.



- Hide quoted text -

- Show quoted text -

Norman,

Thanks again for your efforts to help. I seem to have found the
problem. Obviously no one would be able to troubleshoot a problem as
directly as the particular user, and as has been mentioned, I'm sure
there is an endless list of problems that could reproduce this
particular connection error. Even though it must not seem apparent, I
actually have been "reading the book" on SQL Server Express '05, as
long as it's been out. I would consider myself to be the prototypic
target user - the small office on a budget that would not support a
full-time IT consultant. Although I have not found the introduction
to SSE to be "easy," I've been determined enough to keep
troubleshooting such issues until -with significant help from
contributors like you and Sylvain- the particular snag can be
resolved.

Here's what I learned:

As I mentioned, I believed the test client user was succsessfully
logged on to the Windows server itself (evidenced by such things as
the network status stating that the client was connected, the client
being able to send and receive Outlook/Exchange email messages with
another test client, the client being able to explore shared folders
elsewhere on the network, etc.).

However, those who are professional network administrators would
probably see the problem coming when I explain that I had set up the
test client user on a computer that had previously been used as a
stand-alone desktop. In retrospect I should have discerned that a
type of "hybrid" user account resulted. Even though the startup logon
screen changed from the stand-alone desktop "Welcome" screen to a
"Press Control-Alt-Delete" screen leading to the network logon, the
previous (before client setup) desktop and files were retained.

I established a new "clean" user on the client computer, which was
then able to be recognized as an available network user by SQL Server
Express in Mgmt Studio on the SBS Server. Subsequently I was able to
successfully log on to the SSE instance from the client (both via Mgmt
Studio on the client and via the ADP).

What I'm surprised to see in the many connection-related postings you
commented on are the recommendations to abandon Windows-authenticated
logons for mixed mode or SQL Server logons. If Windows-authenticated
logons are in fact the most secure, it would seem that their use would
be commonplace and relatively uncomplicated to establish.

As I mentioned, one of my greatest frustrations with the (to-be-sure
affordably priced or even free) MS products is the simplicity of a
particular concept against an extraordinarily difficult or snag-prone
execution. My next project is to continue to try to make a linked
server work to connect SQL Server Express to Exchange. So far,
following the MSDN examples and reading the related posts has not
yielded a working result. I would think that tapping into Exchange/
Outlook would be considered one of the most basic operations for a SQL
Server user. If any readers have insight on this and would be willing
to help me, please reply.

 

[Norman Yuan]

Norman,

Thanks again for your efforts to help. I seem to have found the
problem. Obviously no one would be able to troubleshoot a problem as
directly as the particular user, and as has been mentioned, I'm sure
there is an endless list of problems that could reproduce this
particular connection error. Even though it must not seem apparent, I
actually have been "reading the book" on SQL Server Express '05, as
long as it's been out. I would consider myself to be the prototypic
target user - the small office on a budget that would not support a
full-time IT consultant. Although I have not found the introduction
to SSE to be "easy," I've been determined enough to keep
troubleshooting such issues until -with significant help from
contributors like you and Sylvain- the particular snag can be
resolved.

Here's what I learned:

As I mentioned, I believed the test client user was succsessfully
logged on to the Windows server itself (evidenced by such things as
the network status stating that the client was connected, the client
being able to send and receive Outlook/Exchange email messages with
another test client, the client being able to explore shared folders
elsewhere on the network, etc.).

However, those who are professional network administrators would
probably see the problem coming when I explain that I had set up the
test client user on a computer that had previously been used as a
stand-alone desktop. In retrospect I should have discerned that a
type of "hybrid" user account resulted. Even though the startup logon
screen changed from the stand-alone desktop "Welcome" screen to a
"Press Control-Alt-Delete" screen leading to the network logon, the
previous (before client setup) desktop and files were retained.

It seems you have a client/server domain network.In this case all user
account is centrally managed by domain controller. However, a user may still
be able to log onto his computer LOCALLY. A local user account is not
recognized by other computer. So, if you use Windows authentication in SQL
Server, the Windows user account used to access SQL Server must be
recognizeable for the SQL Server, that is, the Windows user account should
be either a domain user account, or local user account on the computer where
SQL Server runs. Then you map SQL Server logins to this type of user
accounts, and assign appropriate access to different databases on the SQL
Server. After that, a user logs into his computer (using a domain account,
or a local account to log in the SQL Server computer) will has expected
access to the SQL Server database(s) without needing to send
username/password to SQL Server. This is Windows Authentication mode of SQL
Server.

I established a new "clean" user on the client computer, which was
then able to be recognized as an available network user by SQL Server
Express in Mgmt Studio on the SBS Server. Subsequently I was able to
successfully log on to the SSE instance from the client (both via Mgmt
Studio on the client and via the ADP).

What I'm surprised to see in the many connection-related postings you
commented on are the recommendations to abandon Windows-authenticated
logons for mixed mode or SQL Server logons. If Windows-authenticated
logons are in fact the most secure, it would seem that their use would
be commonplace and relatively uncomplicated to establish.

These kind of talks on using SQL Server security are usually regarding SQL
Server access from web application. Because web application users are not on
the network domain where the SQL Server runs usually. When you use SQL
Server security, you need to pass username/password through the wire, you
need some means to hide it from hacker. Even you use SQL Server inside your
network, you need either store username/password with the applications
safely (added compexity), or you ask user to enter it each time to access
data and ask them to remember it. However, if you use Windows security, user
only need to log on to his computer. Afterward, his user identity is
recognized anywhere with the network.

As I mentioned, one of my greatest frustrations with the (to-be-sure
affordably priced or even free) MS products is the simplicity of a
particular concept against an extraordinarily difficult or snag-prone
execution. My next project is to continue to try to make a linked
server work to connect SQL Server Express to Exchange. So far,
following the MSDN examples and reading the related posts has not
yielded a working result. I would think that tapping into Exchange/
Outlook would be considered one of the most basic operations for a SQL
Server user. If any readers have insight on this and would be willing
to help me, please reply.

I do not see SQL Server has somethign to do with Exchange/Outlook. You can
send email from one computer to the other, that may prove that your network
is through for email. But you may still have trouble with SQL Server, if it
is not set up correctly.