Re: SQL Connection with .udl and domain accounts
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Fri, 15 Aug 2008 13:08:01 -0400
If you enter your username and password, it's not your DomainName/Username
that is passed to SQL-Server, it's your User Id and password. You are
trying to use a DomainName/UserName in place of an User Id, hence it won't
work because it's not recognised by SQL-Server as a valid User Id.
On the Server Properties in SSMS, check that Security/Server Autentification
is set to either Windows Authentification only mode or to SQL Server +
Windows Authentification mode.
On the Security tabs (not the same as the previous Security option), check
the list of Logins: both Windows logins and SQL-Server logins. Also note
that the SQL-Server Logins won't work if the security property is set to
Windows Authentification Only.
Also, the Windows logins (or Windows Authentification) can only be used with
the NT Integrated Security.
Finally, for your testing problem, you should try the RunAs command:
http://weblogs.asp.net/jgalloway/archive/2007/09/07/tip-use-runas-to-set-your-windows-auth-domain-for-database-connections.aspx
In your case, it's quite possible that you will have SqlWb.exe instead of
SSMSEE.exe. This command can also be run directly from Windows Explorer
contextual menu.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ron" <Ron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:770A5BE0-274F-43CD-B9D8-2073FF2D60DC@xxxxxxxxxxxxxxxx
Hi Sylvain,
Thanks for your response. I appreciate what you are saying about the
difference between a SQL account and a domain account.
However, that is not what I am querying.
Forget the fact that I am a domain admin.
If I fire up the .udl and use integrated authentication - I can connect to
the SQL server. My domain credentials are passed to the SQL server because
it
is the credentials of the logged on user.
If I fire up the .udl and enter a username and password in the form of
domainName\Username - I cannot connect to the SQL server. However, my
domain
credentials are being passed to the SQL server.
What is the difference?
If you examine the TDS passed over the network - both instances above
appear
very similar. UserNames and passwords are encrypted in both cases.
Any help with the following situation is greatly appreciated:
I am trying to test the connectivity for other domain accounts. For
example,
if I set a windows service on the client box to run as a restricted domain
user - I want to confirm the SQL connection is OK from that user. (The
user
would not be able to logon interactively and would only have logon as a
service rights.)
Thanks
"Sylvain Lafontaine" wrote:
Normal. SQL-2005 recognise two types of login: NT Integrated
authentification (also known as Windows authentification or SSPI) and SQL
Login account using a SQL Login name (or User Id) and password that will
be
transmitted in clear over the wire.
A SQL Login Account is *not* a DomainName\UserName account. A SQL Login
Account must be created on the SQL-Server service itself using the SSMS
or
T-SQL. In your case, you are simply making a confusion between a SQL
Login
account and your DomainName\UserName; which is a different thing from a
Sql
login account and cannot be used in place.
You should revise the security information of SQL-Server in the BOL (Book
On
Line).
Notice also that even if you are domain admin on the machine running the
SQL-Server, this windows account can be denied access to the SQL-Server
service with the (im)proper setting. So if you are making tests with the
SQL-Server's security, make sure that you know what you are doing or you
might get locked out of the SQL-Server service and your only option will
be
to desinstall/reinstall it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ron" <Ron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B79672A-438F-40B0-B344-108F3D6C1E92@xxxxxxxxxxxxxxxx
Hey all!
Hope someone can help? Not sure if I'm going nuts :)
I have two boxes both on the same domain. I am a domain admin and so
have
full rights on both. One box is running SQL2005. Lets just call the
other
box
the client. Both OS's are W2K3 server.
On the client box, I create a .udl to test my connection. I open it up
and
choose "MS OLE DB Provider for SQL Server" as my provider. I put my SQL
server name in the connection box and check the box to use NT
integrated
authentication. I test the connection and it works fine!
However, if I uncheck "NT Integrated authentication" and provide my
domain
credentials in the form DomainName\UserName or as a UPN - the test
fails.
I have run a trace using the profiler on the SQL server and I can see
the
successful logons when using integrated auth. This reports my username
in
the
form DomainName\UserName. Oddly enough, when I look at the failed
attempts
using my manually entered credentials, I can see the failure but it
STILL
reports my username exactly as above for the successful logon!
Why is this?
I am trying to test the connectivity for other domain accounts. For
example,
if I set a windows service on the client box to run as a restricted
domain
user - I want to confirm the SQL connection is OK from that user. (The
user
would not be able to logon interactively and would only have logon as a
service rights.)
Your help is greatly appreciated!
Thanks
.
- References:
- SQL Connection with .udl and domain accounts
- From: Ron
- Re: SQL Connection with .udl and domain accounts
- From: Sylvain Lafontaine
- Re: SQL Connection with .udl and domain accounts
- From: Ron
- SQL Connection with .udl and domain accounts
- Prev by Date: Re: SQL Connection with .udl and domain accounts
- Next by Date: Intermittent "access denied" problem with SQL Server Express 2005
- Previous by thread: Re: SQL Connection with .udl and domain accounts
- Next by thread: Intermittent "access denied" problem with SQL Server Express 2005
- Index(es):
Relevant Pages
|
