SQL Server Login Error

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?
----ERROR----
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

dreinmannIT Business Architect - Supply ChainAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
right....

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  
   @server='SQL_MLBIZ',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='MYDOMAIN\MICROSOFTSMLBIZ'

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.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
iss the domain\userid defined in the sql server security, and is it granted permissions to the relevant database?
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the default database for that login?
do you specify a database in the connection string?
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this just looks perfect.
so, there must be something else, which I don't see right now...
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So, you are saying it should work with the 'sysadmin' checked?
I mean, it should work without sysadmin being checked.

0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
Right, sorry...mistyped.

So, I'm missing necessary Server Permissions to connect without it, right?
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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.
0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
Know anything about Custom Bootstrapping in Visual Studio 2005?

If so, having some difficulties, see link below.

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Studio_.NET_2005/Q_24329193.html
0
 
Mark WillsTopic AdvisorCommented:
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...




0
 
dreinmannIT Business Architect - Supply ChainAuthor Commented:
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)

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24333012.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.