SQLEXPRESS 2005 Connection Error

[DrAvi via AccessMonster.com]

Hello,

Using SQLEXPRESS 2005.
As long as I'm trying to enter to the server using Microsoft authentication,
it all works great.
I can also connect Microsoft Office Access Project locally to the server,
providing server name and choosing NT authentication.

The problem starts when I'm trying to connect Microsoft Office Access Project
from remote computer.

When I'm testing the connection using NT authentication I'm getting the
following error msg: "Test connection failed because of an error in
initializing provider. Login failed for user 'CompoterName'\Guest".

When I'm testing the connection using specified User name and Password I'm
getting the following error msg: "Test connection failed because of an error
in initializing provider. Login failed for user 'Username'. The user is not
associated with a trusted SQL server connection".

Why can't I enter using NT authentication, and why not as trusted SQL server
connection?

 

[Belu33fr]

Hi,
Just you must allow in SQLExpress configuration the tcpop connection and you
must verify (if you have a firewall) that firewall allow incoming connection
for the port 1433 (default port for sql)

 

[Norman Yuan]

Since the error says "Login failed", not connecting failed, it is obvious to
me that the connection to the SQL Server Express can be established, so, the
problem seems unlikely being caused by firewall/TCP protocol.

To the OP:

1. Specifying username/password can only be used when the SQL Server is
enabled to use mixed security mode. By default SQL Server Express, only
Windows integrated security is enabled. So, have you enabled mixed security?
If you did not, or do not know what it is or how to, you do not specify
username/password when try to connect. BTW, the username/password is not the
one used for user to log into the computer. It is SQL Server login
username/password.

2. Are the two computers are n a domain network, or a peer-to-peer network?
If it is peer-to-peer network, it is tricky to use Windows integrated
security to connect to SQL Server/Express on other computer. You'd better
use SQL Server security (SQL Server login username/password). Of course you
have to create SQL Server login first.

 

[Belu33fr]

I wrote this solution because I have been this case with Access 2003 and
SqlExpress 2005. Because after the installation of SqlExpress, the tcp
protocol and the firewall on xp and vista are not setup correctly (it is by
design). Maybe, in this case it is different.

 

[DrAvi via AccessMonster.com]

Hi, Thank you both for your answers.

I believe the solution is hidden somewhere in Norman's answer, even though
I've checked according to Belu33fr answer, and the configured server was to
enable remote connection. Just in case, I also disabled all firewalls during
my tests.

I'm new user with SQLEXPPRESS and will appreciate your advice. I suspect the
problem is with the remote user permits.

(BTW, XP PRO on both computers, Access 2003 on remote computer, SQLEXPPRESS
2005 on the host, Access 2007 on the host can easily connect by using Windosw
authentication, error by using SQL authentication).

The remote computer is connected to the computer that host SQLEXPPRESS on the
same domain network. According to the error MSg it seems that the ADP has
found the server but doesn’t have the right permit to login.

I also Enabled SA password. When I'm trying to change the server
authentication from Windows authentication to SQL server authentication, test
on the server itself returns error MSG that "login failed for user {user}.
The user is not associated with a trusted SQL server connection (No 18452)"

The questions are – isn’t Windows authentication enough for remote computer
to connect the server, and how do I associate user with a trusted SQL server
connection.

Thank you

 

[Sylvain Lafontaine]

Depends on what you mean with Window authentication. If, for example,
MyMachine\MyAccount & Password are a valid Windows account with its
password - either on your machine or on the remote machine - then you
*cannot* use these values (account + password) for connecting to a
sql-server instance.

What you can do is to use the integrated security. The database provider
will then use your *current* login account to etablish a connection with the
sql-server. However, this require that the sql-server is either on your
local machine or that your machine is part of a trusted domain by the
machine on which the sql-server is running. It also require that this
Windows account as been granted some database rights on the SQL-Server. By
default, no Windows account has any granted right on an installation of
SQL-Server excerpt for the local Administrators group (and even these can be
revoked if necessary). Notice also that for programs like the web server
(IIS), these programs will use another account (a lot of possibilities here)
then your current account to etablish a connection.

If you cannot use integrated security, then you can create a sql-server
login account on the SQL-Server (notice: on the sql-server and not on the
windows machine running this server) and use this sql-server to login if the
mixed mode of authentication has been enabled. sa is one of these
sql-server account but as it is the super-user account, using it poses some
security risks. Again, you must not confuse the Windows accounts with the
SQL-Server login accounts and remember that by default, most Windows
accounts are not granted any access right to a SQL-Server instance and that
you cannot use any these Windows accounts - even after they have been
granted some access rights - otherwise than with the Integrated Security
option.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[AccessVandal via AccessMonster.com]

Do you have Microsoft SQL Server Native Client installed?

 

[DrAvi via AccessMonster.com]

Hi,

Yes, at the SQL server configuration manger I see the SQL native client
configuration.
Client protocol – all enabled except VIA
Aliases – empty.

Is the problem related to it?

 

[Norman Yuan]

You need to study on how SQL Server security works. When you use Windows
integrated security, it only means that when a user log into a computer on
the network, The widows network already has the user authenticated. When
user is to access SQL Server from that computer, his windows identity is
used to identify who he is, and SQL Server accepts the fact that the user is
who he claim is ONLY when SQL Server knows there is such a windows/network
user account exists. So, you first need to create a SQL Server login that
matches certain windows/domain user account. Say, you create a SQL Server
login, which maps to Domain\YouName account, and make this SQL Server login
a public user to database1. When Domain\YourName user try to access SQL
Server from a computer, this user account's identity is passed to SQL
Server. SQL Server looks its login list, finds a login that is mapped to
that windows/domain user account, then, SQL Server does not need to
authenticate the identity (i.e. ask pasword again), simply accepts the fact
that user account has been authenticated by Windows. So, at this point, the
logging in should be succeeded. Then, SQL Server will determine if this SQL
Server login has permission to access certain database (authorization). In
this case, since the SQL Server login has been assigned as database1's
public user, the SQL Server login (that is the Domain\YourName) will have
access to database1 as public role's given permission.

Hope this is not too long and makes you more confused. SQL Server security a
big topic, but setting up SQL Server login is very basic and is something
you must understand if you want to gain access to SQL Server by yourself.
The other approach would be to let a SQL Server admin to set this up for
you.

 

[DrAvi via AccessMonster.com]

Hi,

I tried today to connect ADP to full version of SQL 2005 (remote computer to
host), and with using sa user and password I successfully connected.

Back to my SQL Express: when I'm trying, on the server itself, to login to
the Database engine with SQL server authentication and using my sa user and
password, I get the following connection error "Login failed for user 'sa'.
The user is not associated with a trusted SQL Server connection. (.Net
SqlClient Data Provider)".
Now, I saw that on the full version of SQL 2005, the Edit server registration
properties (right click on the server name and properties) provides much more
options than the express, one of them is the permissions option, which I
don’t have on SQL Express.

Is that the problem I'm facing to associate user with a trusted SQL server
connection? And if so, how can I do that with SQL Express?

thank you

 

[Belu33fr]

Maybe this is your solution because it's look like the same problems
http://support.microsoft.com/kb/889615/en-us

 

[Norman Yuan]

Since you use 'sa' and its password, that means you want SQL Server
authenticate your connection by SQL Server security rather than Windows
integrated security. In this case, you need to make sure the SQL Server is
set up with mixed security mode enabled (default installation of SQL Server
Express only use Windows integrated security). I am prety sure that is the
case, according to the error message you got.

Use SSMS or SSMS Express to enable mixed security mode to the SQL Server
Express instance, if you REALLY wna to pass username/password in your
connection (DO NOT USE sa for any reason!).

However, whether you use SQL Server security or Windows integrated security,
you still need to learn how to create SQL Server login (again, do not use
the built0in SQL Server login 'sa'), and then connect to SQL Server with
appropriated SQL Server login by either passing username/password, or using
windows user account.

 

[AccessVandal via AccessMonster.com]

Yes, you'll need to create an alias on your remote machine not on the
SQLServer Express. Example

To access the remote machine click - Start - Run. Key in "cliconfg.exe" if
you had installed the Microsoft SQL Native Client". In the General tab, add
TCP/IP and Named Pipes you should see it appears on the right list box and
the order sequence. In the Alias tab, Check radio button "TCP/IP". (and you
can create another alias for named pipes if you want to.)

"192.168.30.123" as the Server Name or "TestServer\SQLExpress" and the Server
alias as anything you want like "MyServer". "TestServer" is your server name,
you can check the name in control panel-system and in the Computer name tab
look for "Full computer name" in XP.

As for the "Dynamically determine port" you should leave it uncheck. If that
still fails, check the SQLServer Express port. It should be the default 1433.
"Dynamically determine port" is screwed up, you may have to repeat with
unchecking and checking it until you get it right.

The good thing of installing the native client is that you don't need to open
the ports in the OS firewall on the remote machine.

On the Server side, you must open the firewall ports since you use the XP or
Vista with SQLServer Express.

To test the connection on the remote machine, go to control panel -
administrative tools - data sources - in the ODBC administrator, choose any
DSN for testing. Click Add button to create new data source. Select "SQL
Native Client" - name your data source and description, Select the Server
Alias as you had created earlier, "MyServer" (may take awhile to detect
connection). Click next - check radio button "SQL Server Authentication",
leave the current check box as default checked. Input user login and password
(use the SA for testing). Click Next - Finish and a Pop up appear "ODBC
Microsoft SQL Server Setup, click the button "Test Data Source". See if it
pass or failed. Click all cancel button if you don not want to save this DSN.