Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server Login Error

Posted on 2009-04-16
Medium Priority
Last Modified: 2012-05-06
Having troubles setting up a Login & User and getting it to be able to connect from a Remote computer....see error below.

It works fine when I log on to the remote cpu with the Domain\user that is the Owner of the Database in the Server.

I think it may not be the connection to the Database in the server, but maybe logging in to the server itself?
System.Data.Odbc.OdbcException: ERROR [28000] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'Domain\UserID'.
ERROR [28000] [Microsoft][SQL Native Client][SQL Server]Login failed for user 'USF\hz12330'.
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()

Open in new window

Question by:dreinmann
  • 8
  • 4
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24163776
iss the domain\userid defined in the sql server security, and is it granted permissions to the relevant database?

Author Comment

ID: 24167508
I just checked the 'sysadmin' box in Server Roles and it started working.

That makes it work, but thats not what I should do for a standard user, is it?

Author Comment

ID: 24167535
As far as the 'User Mapping', I have only the Database checked that has the information being accessed by the program, and the Public role checked in that.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24167873
what is the default database for that login?
do you specify a database in the connection string?

Author Comment

ID: 24167903
Connection String:
Public Shared LogManConnString As String = "Driver={SQL Native Client};Server=ServerName;Database=LogisticsManager;Trusted_Connection=yes;"

Default Database for that user is 'LogisticsManager'.  That's the one with the pertinent program info in it.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24168111
this just looks perfect.
so, there must be something else, which I don't see right now...

Author Comment

ID: 24168145
So, you are saying it should work with the 'sysadmin' checked?

Btw, the Login error I was getting before checking 'sysadmin' was a 'State 11'.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24168200
>So, you are saying it should work with the 'sysadmin' checked?
I mean, it should work without sysadmin being checked.


Author Comment

ID: 24168239
Right, sorry...mistyped.

So, I'm missing necessary Server Permissions to connect without it, right?
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 24169712

How have you set up the server login ?

After adding a linked server, it is reasonably common to add a server login to the linked server for logging into that remote server. That's why checking sysadmin probably worked for you.

for example, if using code to create the linked server, common to see:

EXEC sp_addlinkedserver  

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SQL_MLBIZ'
     ,  @useself = 'FALSE'
     ,  @locallogin =  NULL  
     ,  @rmtuser =  'remote_user'  
     ,  @rmtpassword =  'remote_user_password'  

that way you can set up a "standard" user on the remote and have the linked server login as that user. Otherwise it will be using the current user credentials to try to login on that remote server.

sp_addlinkedsrvlogin Arguments:

[ @rmtsrvname = ] 'rmtsrvname'
Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'
Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE.

A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @locallogin = ] 'locallogin'
Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @rmtuser = ] 'rmtuser'
Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login. rmtuser is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'
Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

Note : If you don't use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query to connect to a linked server when all the following conditions exist:
-  A user is connected to SQL Server by using Windows Authentication Mode.
-  Security account delegation is available on the client and sending server.
-  The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.


Author Comment

ID: 24170911
I was actually using OPENROWSET, so this confused me.

But the answer was to use a Linked Server, and set it up the way you described.

Thanks again Mark.

Author Comment

ID: 24170928
Know anything about Custom Bootstrapping in Visual Studio 2005?

If so, having some difficulties, see link below.

LVL 51

Expert Comment

by:Mark Wills
ID: 24171512
Apologies about that, I should have remembered from your previous posting...

Openrowset can specify username and password, though, a linked server when dealing with differing domains and security is sometimes better.

As far as coding goes, trying to forget (and surprisingly easily), so might pass on that one...


Author Comment

ID: 24171597
That's cool.

Just posted a new one right up your alley.  Need to switch the Bulk load openrowset for csv over to a Linked Server too.   (same as this problem, except with a csv)


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question