Credentials not being passed with remote access

I cannot remotely access SQL Server 2005 with Windows Authentication with a
specific login (I get the error 18452 "this user is not associated with a
trusted login.."). I ran a trace with the profiler and it shows that no
values are being passed for the login/credentials.
I can login to the server locally just fine with the login.
Windows Authentication works with the same login work against another SQL
Server machine.
What could be specific to the login or the server that would cause the
credentials not to be passed.
--
John Shahan> What could be specific to the login or the server that would cause the
> credentials not to be passed.
This could be caused by a firewall issue with domain controller
communication. I've also seen this problem due to a time sync issue between
the client and servers.
Hope this helps.
Dan Guzman
SQL Server MVP
"jp" <jp@.discussions.microsoft.com> wrote in message
news:4B43E6E3-D0F0-4084-AED0-6D4139DA79CB@.microsoft.com...
>I cannot remotely access SQL Server 2005 with Windows Authentication with a
> specific login (I get the error 18452 "this user is not associated with a
> trusted login.."). I ran a trace with the profiler and it shows that no
> values are being passed for the login/credentials.
> I can login to the server locally just fine with the login.
> Windows Authentication works with the same login work against another SQL
> Server machine.
> What could be specific to the login or the server that would cause the
> credentials not to be passed.
> --
> John Shahan|||Dan, thank you for your response. I have seen you post helpful information
for many people before.
We looked at the time sync yesterday and it is close.
The odd thing is that it is specific to one login. Login "johnx" does not
pass credentials from any machine. My other login "johnz" works from any
machine. Also, no other users are reporting this problem with their login.
That being the case, do you still think it could be a firewall issue and if
so, what would I look for in the firewall configuration?
And again, to make it more frustrating, "johnx" can use Windows
Authentication on another SQL Server on the same domain with no problem.
John Shahan
"Dan Guzman" wrote:

> This could be caused by a firewall issue with domain controller
> communication. I've also seen this problem due to a time sync issue betwe
en
> the client and servers.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jp" <jp@.discussions.microsoft.com> wrote in message
> news:4B43E6E3-D0F0-4084-AED0-6D4139DA79CB@.microsoft.com...
>|||> That being the case, do you still think it could be a firewall issue and
> if
> so, what would I look for in the firewall configuration?
If other users can successfully login with Windows authentication from the
same machines that fail with "john", then it's probably not a firewall
issue. Furthermore, since "johnx" can successfully login locally, it
appears "johnx" is setup correctly in SQL Server.
The main difference between local and remote access is the communication
protocol. Local access usually uses LCP but remote access is usually done
via TCP/IP or Named Pipes (as specified by the cliconfg.exe utility). Named
pipes requires the Windows account to have the "access this computer from
the network" right so make sure "johnx" has the right (via group membership)
if you are using Named Pipes.
After a successful connection of your working account, you can verify the
connection protocol with:
SELECT net_library
FROM master..sysprocesses
WHERE spid = @.@.spid
If you still can't sort this out, try to connect using SSMS and post the
full error message text here.
Hope this helps.
Dan Guzman
SQL Server MVP
"jp" <jp@.discussions.microsoft.com> wrote in message
news:FC3706EE-CE61-47EE-85A4-DEDDA3A633F6@.microsoft.com...[vbcol=seagreen]
> Dan, thank you for your response. I have seen you post helpful
> information
> for many people before.
> We looked at the time sync yesterday and it is close.
> The odd thing is that it is specific to one login. Login "johnx" does not
> pass credentials from any machine. My other login "johnz" works from any
> machine. Also, no other users are reporting this problem with their
> login.
> That being the case, do you still think it could be a firewall issue and
> if
> so, what would I look for in the firewall configuration?
> And again, to make it more frustrating, "johnx" can use Windows
> Authentication on another SQL Server on the same domain with no problem.
> --
> John Shahan
>
> "Dan Guzman" wrote:
>|||Dan, thanks again for your help. I wanted to share what apparently turned
out to be the solution.
johnx was set up as a local login on sql server. Removing that login
allowed windows authentication to work,
John Shahan
"Dan Guzman" wrote:

> If other users can successfully login with Windows authentication from the
> same machines that fail with "john", then it's probably not a firewall
> issue. Furthermore, since "johnx" can successfully login locally, it
> appears "johnx" is setup correctly in SQL Server.
> The main difference between local and remote access is the communication
> protocol. Local access usually uses LCP but remote access is usually done
> via TCP/IP or Named Pipes (as specified by the cliconfg.exe utility). Nam
ed
> pipes requires the Windows account to have the "access this computer from
> the network" right so make sure "johnx" has the right (via group membershi
p)
> if you are using Named Pipes.
> After a successful connection of your working account, you can verify the
> connection protocol with:
> SELECT net_library
> FROM master..sysprocesses
> WHERE spid = @.@.spid
> If you still can't sort this out, try to connect using SSMS and post the
> full error message text here.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jp" <jp@.discussions.microsoft.com> wrote in message
> news:FC3706EE-CE61-47EE-85A4-DEDDA3A633F6@.microsoft.com...
>|||> johnx was set up as a local login on sql server. Removing that login
> allowed windows authentication to work,
I had assumed johnx was a domain user rather than a local Windows account.
I'm glad you got it working.
Dan Guzman
SQL Server MVP
"jp" <jp@.discussions.microsoft.com> wrote in message
news:36AE4208-0F21-4B0C-A735-4F9663FC812C@.microsoft.com...[vbcol=seagreen]
> Dan, thanks again for your help. I wanted to share what apparently turned
> out to be the solution.
> johnx was set up as a local login on sql server. Removing that login
> allowed windows authentication to work,
> --
> John Shahan
>
> "Dan Guzman" wrote:
>

Credentials Error

I have been trying to use stored credentials with a data source, but I always
get a "login failed" with "Not associated with a trusted SQL Server
connection" as the reason. I am running Report Server on Windows 2000. The
login I am trying to use exists in SQL Server. I have tried different logins
and different servers, always with the same results. Any suggestions?SQL may be set to only allow windows authentication. In enterprise manager,
go to the properties of your server --> security tab and check the
authentication section.
Mike G.
"Falcon" <Falcon@.discussions.microsoft.com> wrote in message
news:10F85238-51F3-466F-9A07-1831B8C0688F@.microsoft.com...
>I have been trying to use stored credentials with a data source, but I
>always
> get a "login failed" with "Not associated with a trusted SQL Server
> connection" as the reason. I am running Report Server on Windows 2000. The
> login I am trying to use exists in SQL Server. I have tried different
> logins
> and different servers, always with the same results. Any suggestions?

creating users in sql eypress

Hi,

wanted to add a login in via c# to sql server express.

Is this possible?

I treid this:

String connString = "data source=xtrlt027;Initial catalog=master;Integrated Security=true;";
SqlConnection conn= new SqlConnection();;
conn.ConnectionString = connString;

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand("create login tommtk with Password='tomm2tomm'; use master; create user tommtk;", conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Login created!!!");

}
catch (Exception deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}

But i get an exception, that there is an error near the keyword login and the keyword user.

So wahts wrong

can anybody help me?

Greetz
What is the exact error message ?

jens Suessmeyer.

http://www.sqlserver2005.de

|||Pretty sure each of you commands inside the cmd object need to be execute as separate statements/commands, not as one.|||Hi,

really looks like, that it is not possible to create logins in sql server express.
Downloaded sql studio management express and in an sql query it did not even recognized the key words.

The exception in my app was:

SQL syntax error near 'login'

So is it really not possible to create logins in sql express? i have the version that is included in visual Studio 2005 professional.

Greetz
|||It absolutely is possible can you please post the script you are trying to run in SSMS-E?|||

I assure you that it is possible to create Logins in SQL Express.

You need to separate certain commands for SQL to recognize them. All the CREATE commands fall into this category. You're code has them all run together as one big statement, which isn't going to work regardless of where you try to run them.

Here is some T-SQL that will accomplish what you're trying to do from the query window. You should be able to translate this to your code by simply running each statement separately. (e.g. Each thing between the GO keywords.)

USE master
GO

CREATE LOGIN tommtk
WITH PASSWORD = N'tomm2tomm'
GO

USE AdventureWorks
GO

-- For login tommtk, create a user in the database
CREATE USER tommtk
FOR LOGIN tommtk
WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'tommtk'
GO

I've actually changes the database context from master to AdventureWorks to create my user as I can't imagine why you were actually trying to create the User in master. If you really were tyring to create a user with permissions in master, just ignore the 'USE AdventureWorks' statement in your code. I've also added a call to give the User a specific database role, db_owner in this case, but that isn't required if you were not planning on assigning a specific role.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||Tried the following sql statement:

create login tommtk
with Password='tomm2tomm';
use master;
create user tommtk
|||Hi,

tried this statement in a developers edition:

create login tommtk with Password='tomm2tomm'; use master; create user tommtk

it worked fine, but not in the express edition. Or what edition ist it, included in VisualStudio 2005.

In the query window it does not even recognize the keyword login.

And in permission-properties i can not switch to mix of windows auth and sql server auth.
It is inactive

Greetz
|||Are you currently running windows Auth only?

Creating user from application

I'm trying to create a user from a powerbuilder application through powerscript.
I'm trying to create first the login using sp_addlogin
but I'm getting the following error
[microsoft][odbc for sql server]the procedure sp_addlogin cannot be executed in transaction.

Plz does someone know what should I do?CREATE Procedure CreareUser (@.UserName varchar(30), @.UserPass varchar(30), @.DBName varchar(30),@.DreptScriere bit)

as

if exists (select * from master.dbo.syslogins where loginname=@.UserName)

exec sp_dropuser @.UserName
exec sp_droplogin @.UserName


if not exists (select * from master.dbo.syslogins where loginname = @.UserName)
BEGIN

EXEC sp_addlogin @.UserName, @.UserPass, @.DBName
EXEC sp_defaultdb @.UserName,@.DBName

if @.DreptScriere=0
BEGIN
EXEC sp_adduser @.UserName,@.UserName,
'db_denydatawriter'
EXEC sp_changegroup 'db_owner' ,@.UserName
EXEC sp_addrolemember 'db_denydatawriter' ,@.UserName
END
else
BEGIN
EXEC sp_adduser @.UserName,@.UserName,'db_owner'
END

EXEC sp_grantdbaccess @.UserName

END

creating user and login problem

When I try to create user using ent manager in mssql2000 it gives me error that there is already such user.But it actualy doesn't exists.

How to solve this problem?

Hi,

did you check your logins by T-SQL?

Code Snippet

SELECT CAST(loginname AS CHAR(30)) AS loginname, sid, status, denylogin

FROM master..syslogins

ORDER BY sid

|||

when you say User... i am not sure , whether u r mentioning Login or Database User. I assume that it is Database user. In that case check whether there is any database role of the same name. The user name has to be unique for both users and role.

if the below mentioned query returns row means there is some role exists in the datbase with the same name.

select *from sysusers where name='YourUserName' and issqlrole=1

Madhu

|||

Thanks for Answer

I didn't find any such role or user name in the database.

But When I connect through "sql server management tool" it lets me create the user.

but the ent manager of 2000 doesn't allow.

|||

post the exact error no and description. also check in Event log for any clue

Madhu

Creating user

Hi,
I've just installed MSDE, and I'm wondering how to create user so I
don't have to login as 'sa' user.
Thanks in advance
ukasz
Try:
sp_addlogin '<userName','<password>'
Look for sp_addlogin / sp_grantlogin in the BOL.
HTH, Jens Suessmeyer.
|||Hi,
To add on Jens:-
MSDE will not come with GUI tools; so you need to login using OSQL to sql
server and issue TSQL commands to create Logins and users.
For eg; if you need to create a user HARI with read only rights in Finance
database then:-
From command line:-
OSQL -S <servername> -Usa -P<sapassword> (enter)
SP_addlogin 'hari','haripass'
go <enter>
Use Finance
go <enter>
sp_adduser 'hari'
go <enter>
sp_addrolemember 'db_datareader','hari'
go <enter>
he above commands will create user Hari with read rights in Finance
database.
Thanks
Hari
SQL Server MVP
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126692828.991504.289480@.g47g2000cwa.googlegr oups.com...
> Try:
> sp_addlogin '<userName','<password>'
> Look for sp_addlogin / sp_grantlogin in the BOL.
> HTH, Jens Suessmeyer.
>
|||Hari Prasad wrote:
> Hi,
> To add on Jens:-
> MSDE will not come with GUI tools; so you need to login using OSQL to sql
> server and issue TSQL commands to create Logins and users.
> For eg; if you need to create a user HARI with read only rights in Finance
> database then:-
> From command line:-
> OSQL -S <servername> -Usa -P<sapassword> (enter)
> SP_addlogin 'hari','haripass'
> go <enter>
> Use Finance
> go <enter>
> sp_adduser 'hari'
> go <enter>
> sp_addrolemember 'db_datareader','hari'
> go <enter>
> he above commands will create user Hari with read rights in Finance
> database.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126692828.991504.289480@.g47g2000cwa.googlegr oups.com...
>
>
Thanks.
|||Hari Prasad wrote:
> Hi,
> To add on Jens:-
> MSDE will not come with GUI tools; so you need to login using OSQL to sql
> server and issue TSQL commands to create Logins and users.
> For eg; if you need to create a user HARI with read only rights in Finance
> database then:-
> From command line:-
> OSQL -S <servername> -Usa -P<sapassword> (enter)
> SP_addlogin 'hari','haripass'
> go <enter>
> Use Finance
> go <enter>
> sp_adduser 'hari'
> go <enter>
> sp_addrolemember 'db_datareader','hari'
> go <enter>
> he above commands will create user Hari with read rights in Finance
> database.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126692828.991504.289480@.g47g2000cwa.googlegr oups.com...
>
>
Thanks
ukasz

creating temp tables during login

Frenz,

I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.

Your response is highly appreciated.

-CheekuEnsure the TEMPDB has not restricted to a max. size, if so it may fail.
And ensure the server is not stressed with this approach. In general the temp tables will be dropped once the session is closed at the SQL server.
Check books online for more information on temp. tables topic.|||annonymus block

Holy Oracle Batman

And Why do you need to do this?

Creating SQL Login

I'm trying to create an sql server login, which should have access to and may see only one database. So what I do, is create a login and set the db's owner to this login. I also give it the appropriate rights like public, datareader, datawriter etc. on this db.

Furthermore, the login has no rights on other databases on the sql server. However, the login can see the other databases if he registers the sql server with the provided login data. This is what we would like to prevent: he may only see his own database.

How could I tackle this problem? Any suggestions are welcome.taken from books online

sp_addlogin
Creates a new Microsoft® SQL Server? login that allows a user to connect to an instance of SQL Server using SQL Server Authentication.

Syntax
sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt = ] 'encryption_option' ]

Arguments
[@.loginame =] 'login'

Is the name of the login. login is sysname, with no default.

[@.passwd =] 'password'

Is the login password. password is sysname, with a default of NULL. After sp_addlogin has been executed, the password is encrypted and stored in the system tables.

[@.defdb =] 'database'

Is the default database of the login (the database the login is connected to after logging in). database is sysname, with a default of master.

[@.deflanguage =] 'language'

Is the default language assigned when a user logs on to SQL Server. language is sysname, with a default of NULL. If language is not specified, language is set to the server's current default language (defined by the sp_configure configuration variable default language). Changing the server's default language does not change the default language for existing logins. language remains the same as the default language used when the login was added.

[@.sid =] sid

Is the security identification number (SID). sid is varbinary(16), with a default of NULL. If sid is NULL, the system generates a SID for the new login. Despite the use of a varbinary data type, values other than NULL must be exactly 16 bytes in length, and must not already exist. SID is useful, for example, when you are scripting or moving SQL Server logins from one server to another and you want the logins to have the same SID between servers.

[@.encryptopt =] 'encryption_option'

Specifies whether the password is encrypted when stored in the system tables. encryption_option is varchar(20), and can be one of these values.|||Well, I already did the same thing but then using the the Enterprise Manager menu's. Problem remains how to make sure the sql login cannot SEE the databases it has no access to... furthermore, I see the guest account also has rights to the master and tempdb databases, while this account may not be deleted according to msdn documentation... Could I solve this problem or is it just simply impossible?

Creating new login on SQL server

hello,
I am facing problem to create a new login account on SQL server
which is on different domain.
Can any one help me out?
Thanks.It is OK if the domains have a trust relationship - if not you can't do it.
"Sushi" <sushi_20003@.yahoo.co.uk> wrote in message
news:8f9a3b2.0403142131.7a420e66@.posting.google.com...
> hello,
> I am facing problem to create a new login account on SQL server
> which is on different domain.
> Can any one help me out?
> Thanks.|||Hi,
Are you able to connect to SQL server using SQL server authentication, If
yes, then you will not have any problems in creating the login.
1. Connect to SQL Server using SA user
2. Use SP_ADDLOGIN (refer books online) to add the login
3. Use SP_ADDUSERS (refer books online) to add the user to database
4. Use SP_ADDROLEMEMBER to add the database role to that user.
Note:
If you are not able to connect to the SQL server,Check with system
administrator on Trust Relationship. If it is not created ask him to
create the Trust relationship and then perform the above steps to create
Login.
Thanks
Hari
MCDBA
"Sushi" <sushi_20003@.yahoo.co.uk> wrote in message
news:8f9a3b2.0403142131.7a420e66@.posting.google.com...
> hello,
> I am facing problem to create a new login account on SQL server
> which is on different domain.
> Can any one help me out?
> Thanks.

creating login2 with same password as login1

how can I create a sql login login2 with the same password as login1 if I am an administrator and I don t know what the password of login1 is?

Thanks

P.S: It s just that I m creating script automating changing domain name in sql logins and sql DB users becuase that didn't happen automatically after a windows migration

Thank you.

See this KBA http://support.microsoft.com/kb/246133 is any help.|||

exactly what i needed. I thought things d be easier than that :)

Thanks

creating login2 with same password as login1

how can I create a sql login login2 with the same password as login1 if I am an administrator and I don t know what the password of login1 is?

Thanks

P.S: It s just that I m creating script automating changing domain name in sql logins and sql DB users becuase that didn't happen automatically after a windows migration

Thank you.

See this KBA http://support.microsoft.com/kb/246133 is any help.|||

exactly what i needed. I thought things d be easier than that :)

Thanks