?
Solved

Can't see database when trying to select server in ODBC data source?

Posted on 2008-11-06
26
Medium Priority
?
575 Views
Last Modified: 2012-05-05
I just added a new database onto a Windows 2003 Server. Database is on SQL Server 2005. On client PC, I want to create a new ODBC data source, but the server does not appear in the server list and when I type in the server name, it says the connect failed, SQLState 28000, error 18456 and login failed. I have select Windows authenication using network login. Can you help connect?
0
Comment
Question by:gtrapp
  • 11
  • 6
  • 3
  • +2
23 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 22902466
have you used the config tool to allow remote connections?
is the server set to mixed mode authentication?
is your windows login mapped to a user with access to the new db?
0
 

Author Comment

by:gtrapp
ID: 22906873
In SQL Server, I have "Allow remote connections to this server" set
In the SQL Server Surface Area Configuration, I have "Local and remote connections" and "Using TCP/IP only" set
The problem is likely with the Windows login account. I have another SQL Server and I can connect to DBs on that server via ODBC using my NT account. We do use Active Directory here, and I am in the
Group - IT DB Admins. The database that I am trying to access on the new server is the same database. I am moving the database from the old server to the server, so the DB has the same Roles, Users, and such.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 22923400
Check if you have SQL Server 2005 Client installed in client PC.
You'll need that to connect do database.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 22923438
If you are using SQL Server with ASP.NET, it is preferable to use the .NET SQL Server data provider over ODBC.  You can just use a connection string with System.Data.SqlClient classes, such as the SqlDataAdapter or an SqlDataSource.

Example:

      Data Source=.\DEVELOPER;Initial Catalog=CrystalReports;Persist Security Info=True
0
 

Author Comment

by:gtrapp
ID: 22924908
I am not using ASP.Net. I need to use an ODBC data source. An client application needs this to connect to the DB.
I am new to NT logins and not sure what to do. I have one server with DBs on SQL Server and I can connect to all DBS with my NT login account. But, with the same account, can't connect to DB on this new SQL Server installation.
0
 

Author Comment

by:gtrapp
ID: 22925841
Yes, sorry. Its really a NT login issue.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22927396
How are you trying to connect to the SQL Server?  Can you PING the database server?

Reference:

Troubleshoot Connectivity Issue in SQL Server 2005 - Part II
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx
0
 

Author Comment

by:gtrapp
ID: 22932589
Yes, I can ping.

I have an administrator account named agtrapp. This connects to the DB.
When I use my gtrapp, it does not connect to the DB.

The problem, I think, has nothing to do with SQL Server, its my NT account.
 
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 22932733
agtrapp domain should be the same (or be trusted) of SQL Server domain.
It is?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22933369
What is the SQL Server's authentication mode?
0
 

Author Comment

by:gtrapp
ID: 22933424
It is "SQL Server and Windows Authenication mode"
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22933496
For mixed mode, you can either specify Integrated Security=True (Windows Authentication), or User ID=<user> and Password=<password> in the connection string.  I am going to assume that you have allowed remote connections (as mentioned before), and that you have a valid connection string.
0
 

Author Comment

by:gtrapp
ID: 22933534
I have "allow remote connections" set. I just have not figured out how to allow my gtrapp account to connect to the DB. Also, other non-admin accounts can not connect either.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22933557
Which connection string form are you using (it sounds like Windows Authentication)?  Does your your server have any logins and your database have any users defined?
0
 

Author Comment

by:gtrapp
ID: 22934597
Yes, I am using Windows Authenication. I am trying to create an ODBC data source via of the Create new Data Source wizard. This is a System DSN I am trying to create. I can see the server name now in the drop down list box. Server name is Tomlinson.  When creating, message says

Connection Failed
SQLState 28000
Server Error: 1845
[Microsoft][ODBC SQL Server Driver][Login Failed for user PBTF\gtrapp

The database on the new server has the same logins has on the older server. Its the same database. I just moved it to the new server. I have been adding logins to the SQL Server logins, not nothing works so far.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22934974
1) The server has Logins and the database has Users.  Are you saying that the new server has the same logins as the old server?

2) Do you have other test tools, like SQL Server Management Studio?
0
 

Author Comment

by:gtrapp
ID: 22935066
The database has the same logins as the old server, which everybody could connect to. The server does not have the same logins as the old server, but it has many of the same ones. The old server has some that are not neccessary on the new server. These old logins are related to other applications..

I don't have SQL Server Management Studio installed on this server.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 22936014
One thing I've see with "old logins" the associated password is nice ans short and easy to remember... but after updates to SQL Server and updates to the O/S, those old passwords do not meet policy requirements (usually 8 characters, with at least one non-alpha, an perhaps one special).
Is the current password short?  If so (for diagnostic purposes) change it to a longer password -- and use the new/better password when working with the ODBC Administrator.
0
 
LVL 29

Expert Comment

by:QPR
ID: 22936365
Have just skimmed through the p[revious comments so apologies if I missed something already mentioned....
How did the original logins get to the new server? Were they recreated or restored?
I've found problems when transferring logins before - something about the SID being incorrect.
Try dropping one of the NT logins and recreating it. Then try to connect using that login. If nothing else it will cancel out that thought.
0
 

Author Comment

by:gtrapp
ID: 22961264
I reset the passwords on the logins and still can not connect

The server is new, so the accounts/logins are the defualts. The logins in DB that I am trying to connect to were brought over from the old database. The DB was moved to the new server. I created a BAK and retored the DB on the new. I am trying to create a ODBC data source from a client and connect to the new.

I have an agtrapp account with full admin permissions. This connects to the DB. When I use my gtrapp, regular NT account, it does not connect. However, my regular NT account used to connect to the old server via ODBC data source.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 22961444
gtrapp, have you checked if the domain of the user and the server where's the new DB are the same?
Because if they aren't you need to have both domains trusted.
0
 

Author Comment

by:gtrapp
ID: 22965522
Yes, I have checked the domain name infront of the logins. The new server is in the same domain as the old.

I like to ask some one here who has some knowlegde with setting up NT accounts and see if he can help. I will keep you posted.
0
 

Accepted Solution

by:
gtrapp earned 0 total points
ID: 22986507
I have this fixed now. It was an issue with Active Directory not refreshing properly. Thanks for all of your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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