Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about "trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in another windows account (Windows "Run As" feature) if you want to authenticate differently. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...
> Hi, >
> I'm attempting to connect to a SQL Server 2005 instance on a Windows
> 2003 server remotely with Management Studio on a Windows XP box. SQL
> Server is set to use Windows Authentication. >
> If I select Windows Authentication as the authentication method in
> Management Studio, the 'user name' field is greyed-out and locked to my
> current XP user name, which is no good for connecting to the server. >
> Why is this happening and how can I change the user name and password to
> that of an actual account on the server? >
> TIA, >
> CraigyBoop >
>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.
|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about "trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in another windows account (Windows "Run As" feature) if you want to authenticate differently. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...
> Hi, >
> I'm attempting to connect to a SQL Server 2005 instance on a Windows
> 2003 server remotely with Management Studio on a Windows XP box. SQL
> Server is set to use Windows Authentication. >
> If I select Windows Authentication as the authentication method in
> Management Studio, the 'user name' field is greyed-out and locked to my
> current XP user name, which is no good for connecting to the server. >
> Why is this happening and how can I change the user name and password to
> that of an actual account on the server? >
> TIA, >
> CraigyBoop >
>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.
|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de
sql

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about

"trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in

another windows account (Windows "Run As" feature) if you want to authenticate differently.

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...

> Hi,

>

> I'm attempting to connect to a SQL Server 2005 instance on a Windows

> 2003 server remotely with Management Studio on a Windows XP box. SQL

> Server is set to use Windows Authentication.

>

> If I select Windows Authentication as the authentication method in

> Management Studio, the 'user name' field is greyed-out and locked to my

> current XP user name, which is no good for connecting to the server.

>

> Why is this happening and how can I change the user name and password to

> that of an actual account on the server?

>

> TIA,

>

> CraigyBoop

>

>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

Authenticating against LDAP/Active Directory with SQL Server 2000 SP2

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.

Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI', @.srvproduct=N'Active
Directory Services 2.5', @.provider=N'ADsDSOObject', @.datasrc=N'adsdatasource'

Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @.userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @.userId
)

So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')

ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'

So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

Why do you need to validate credentials on a SQL server?

If a password is entered on a client, then could you validate it there or are you going to do something as that user on a SQL server?

In that case could you impersonate a user on a client and connect to the server?

|||The app that needs this validation cannot validate against LDAP directly; it is a limited custom-built scripting language. It can easily execute queries against its remote SQL Server DB, and we are trying to investigate a solution that will allow us to run a query to validate against LDAP from there. Simply knowing whether or not the user ID/password combination entered into the app matches the Active Directory credentials is sufficient.

Edit: From browsing more formus/docs today, what do you think of using an extended stored procedure? Possibly compile the .NET connection code in a dll and plug it into sql server? From digging through more postings it looks like it might be the most direct route I'll be able to get.

Thanks for the post!
|||That extended procedure did the trick!

We ended up using IADsOpenDSObject:: OpenDSObject for this purpose. We were constrained to using C/C++ and none of the .NET classes, but there was very nice documentation in several places that served as great guides:

http://www.codeproject.com/database/extended_sp.asp
http://msdn2.microsoft.com/en-us/library/aa706065.aspx

I did see where MS noted that "This method should not be used just to validate user credentials," but the link they provided to SSPI authentication was not very helpful.

ALSO, for anyone else jumping through the same hoops, MS noted that the function srv_describe (and its companion srv_ functions) is being removed in future versions of SQL Server:

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

They state to "Use CLR Integration instead," so if anyone has any links to a good reference I'd love to check them out!

Authenticating against LDAP/Active Directory with SQL Server 2000 SP2

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.

Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI', @.srvproduct=N'Active
Directory Services 2.5', @.provider=N'ADsDSOObject', @.datasrc=N'adsdatasource'

Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @.userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @.userId
)

So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')

ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'

So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

Why do you need to validate credentials on a SQL server?

If a password is entered on a client, then could you validate it there or are you going to do something as that user on a SQL server?

In that case could you impersonate a user on a client and connect to the server?

|||The app that needs this validation cannot validate against LDAP directly; it is a limited custom-built scripting language. It can easily execute queries against its remote SQL Server DB, and we are trying to investigate a solution that will allow us to run a query to validate against LDAP from there. Simply knowing whether or not the user ID/password combination entered into the app matches the Active Directory credentials is sufficient.

Edit: From browsing more formus/docs today, what do you think of using an extended stored procedure? Possibly compile the .NET connection code in a dll and plug it into sql server? From digging through more postings it looks like it might be the most direct route I'll be able to get.

Thanks for the post!
|||That extended procedure did the trick!

We ended up using IADsOpenDSObject:: OpenDSObject for this purpose. We were constrained to using C/C++ and none of the .NET classes, but there was very nice documentation in several places that served as great guides:

http://www.codeproject.com/database/extended_sp.asp
http://msdn2.microsoft.com/en-us/library/aa706065.aspx

I did see where MS noted that "This method should not be used just to validate user credentials," but the link they provided to SSPI authentication was not very helpful.

ALSO, for anyone else jumping through the same hoops, MS noted that the function srv_describe (and its companion srv_ functions) is being removed in future versions of SQL Server:

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

They state to "Use CLR Integration instead," so if anyone has any links to a good reference I'd love to check them out!

Authenticate via Active Directory from PC that's not joined the do

I have several people on my team that are working remotely from PCs that are
stand-alone, ie. they have not been joined to the domain. They do have an
account on the domain, though, which they use to map drives and such. How
can I set it up so that they can use Enterprise Manager or Query Analyzer by
authenticating with their domain account?Hello,
I suggest that you create a SQL login for each user. You can refer to the
following article:
Adding a SQL Server Login
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_9m0e.asp
How to set up Mixed Mode security (Enterprise Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Make sure SQL server use mixed mode Authentication.
If the client can join the domain with a domain user account, you can add
the domain user account as a Windows login. You can refer to the following
web site:
How to grant a Windows user or group login access to SQL Server (Enterprise
Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Granting a Windows User or Group Access to a Database
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_2wit.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.