Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

After MSDE install, can't connect as SQL Server

Status
Not open for further replies.

sharonniles

Programmer
May 31, 2001
38
US
I've installed MSDE, with SAPWD, SecurityMode=SQL, DisableNetworkProtocol=0. The SQL Server Server Manager is on the desktop, and all three services are Green (running). I also disabled the Firewall, virus and spyware programs. But I can't connect to anything. When I'm in Access, trying to view an .ADP file, the error message is: "Microsoft Access could not find either Microsoft SQL Server 2000 Desktop Edition or Microsoft SQL Server installed on your computer."

When I check DataLink, I click on Microsoft OLE for Servers. The error message is: Provider no longer available. Ensure the provider is installed correctly.

Now one thing that may be a conflict, several months ago I installed a trial Microsoft SQL Server (professional version). I deleted everything associated with it. I removed the files, registry, etc.

Anyhelp will be most appreciated.

Sincerely
 
The trial from SQL Server shouldn't cause any problems. The only file on the trial that should be disabled is the sqlservr.exe.

For the MSDE install, check the ERRORLOG file. Should be somewhere like c:\program files\microsoft SQL Server\MSSQL\Log. There there are any problems with it starting, they should show up in there.

Denny

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(My very old site)
 
Thank you Mr. Denny for your reply. I was finally able to install Crystal Enterprise 10 with MSDE. The same problem persists, though. I'm still unable to connect to anything. The SQL Server Service Manager is green and ready to go. I turned off the Firewall, McAfee and SpySweeper. In the Registry, the LoginMode is 2.
I cannot log into Web Data Administration on either Windows Authentication or SQL Server. When I enter the UserName or Password, I get an hourglass and then nothing happens. Any help will be most appreciated.

Thanks,
Sharon


2005-02-15 19:25:55.56 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

2005-02-15 19:25:55.56 server Copyright (C) 1988-2002 Microsoft Corporation.
2005-02-15 19:25:55.56 server All rights reserved.
2005-02-15 19:25:55.56 server Server Process ID is 876.
2005-02-15 19:25:55.56 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'.
2005-02-15 19:25:55.76 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2005-02-15 19:25:57.96 server SQL Server configured for thread mode processing.
2005-02-15 19:25:58.03 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2005-02-15 19:26:01.73 spid3 Starting up database 'master'.
2005-02-15 19:26:03.76 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-02-15 19:26:03.76 spid5 Starting up database 'model'.
2005-02-15 19:26:03.82 spid3 Server name is 'P4'.
2005-02-15 19:26:03.82 spid3 Skipping startup of clean database id 4
2005-02-15 19:26:03.86 spid3 Starting up database 'CE10'.
2005-02-15 19:26:04.85 spid5 Clearing tempdb database.
2005-02-15 19:26:05.35 server SQL server listening on 192.168.1.100: 1433.
2005-02-15 19:26:05.35 server SQL server listening on 127.0.0.1: 1433.
2005-02-15 19:26:05.43 server SQL server listening on TCP, Shared Memory, Named Pipes.
2005-02-15 19:26:05.45 server SQL Server is ready for client connections
2005-02-15 19:26:06.37 spid5 Starting up database 'tempdb'.
2005-02-15 19:26:06.49 spid3 Recovery complete.
2005-02-15 19:26:06.53 spid3 SQL global counter collection task is created.
2005-02-15 20:59:33.62 spid53 Starting up database 'msdb'.
2005-02-15 20:59:35.07 spid53 Using 'xpsqlbot.dll' version '2000.80.760' to execute extended stored procedure 'xp_qv'.
 
I see from the log that you posted that the database is listening on port 1433 on both localhost and 192.168.1.100.

Try to telnet to port 1433 on the machine. That will tell you is something is blocking the SQL Server from getting the connection.
This will telnet to the port.
Code:
telnet P4 1433
If you get a blank screen with a flashing cursor it connected. If you get an error message it did not.

Denny

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(My very old site)
 
Thank you Mr. Denny. I did telnet P4 1433 and I get a blank screen. At the DOS prompt, when I enter OSQL -U sa and press return, I type in my password and it's accepted. I was able to login to Web Admin using Windows authentication, not SQL login. On CE10, I'm not able to login with Windows nor SQL Login. But the database is working in the Crystal Configuration Manager.

I appreciate your help. Are you a Crystal Enterprise user?

Sharon
 
Nope, I don't use Crystal. I'm not sure why the web admin isn't letting you log in correctly. If you can log in wish osql you should be able to log in with anything.

Mabey the SQL Drivers got hosed some how. You can try reinstalling them from the SQL CD.

Denny

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(My very old site)
 
Sharon,

There's no 'failed login' message in the Error log you provided. Do you have AUDIT turned on? Are you getting failed login messages in the SQL Server Error log when you use CR10?

If you are auditing for failed logins in SQL Server, but aren't seeing any failed login messages in the SQL Server Error log, then something on the operating system side is stopping the connection and you aren't even getting to the Database.

This is usually (in my experience) due to either driver issue (as mrdenny pointed out) or due to incorrect ODBC connection setup.

DISCLAIMER: I'm not a Crystal Report guru, but I do use and support CR8.5 and CR9. I have to help my users with CR when they have problems getting data from my database.

-SQLBill

Posting advice: FAQ481-4875
 
Thank you MrDenny and SQLBill. I was able to get MSDE working, but in a crippled kind of way. I didn't have the DataSource (ODBC) setup. The Web Data Administrator works and logs in SQL Authorization. No Problem. But in Business View in Crystal Enterprise, I can only login as Administrator. I still get the error, "Enterprise Authorization could not log you on. Make sure that your logon information is correct." I don't know why CE doesn't recognize the logon.

Sharon Niles
 
Let's make sure the obvious is taken care of....

The SQL Server you are trying to connect to....is it set up to use Windows Authentication ONLY or Mixed Mode?

On the SQL Server, do you have a Login? Is is set for Windows Authentication or SQL Server Authentication?

How many installations of SQL Server versions are there?
(So far I'm counting 2. One Web-based and One installed as part of Crystal Reports (MSDE version)).

If you have more than one version, how does CR know which one to use? You need to set up an ODBC connection for that. Do you have one set up?

-SQLBill

Posting advice: FAQ481-4875
 
Hi SQLBill,

Thank you so much for helping me. I think you hit on something critical.
1) In the registry, I have LoginMode at 2 for MixedMode.
2) UserDSN
Name: Enterprise
Description: SQL Server
Which SQL Server do you want to connect to:
there are 3 listed
(local)
P4
P4

Next==>
How should SQL verify authenticity of the login:
with SQL server.
the connect to SQL server box is checked.
login: sa
password: (same as SQL server)

The name of the computer is P4. Also, the section under Inetpub, is called P4 also.


Is local the same as one of the P4s? How did I install two versions and how do I find and delete?

I appreciate your help.
 
(local) is the one on the same computer as you are working on.

You shouldn't be using the SA account (really).

In Enterprise Manager, drill down to Security. Expand that. Click on Logins. Do you have a login? If not, then create one. Make sure you select SQL Server Authentication and provide a password. Make sure you go to the Database Access tab and provide proper access to the database. OR go to the Server Roles and give yourself System Admin privileges (if you are the Database Admin).

Next, in Windows, go to Start>Control Panel>Administrative Tools>Data Sources (ODBC). Find the ODBC connection you are using for Crystal Reports. For server name, enter the IP address. Then continue through the windows until you get to one that allows you to set login. Click Client Configuration. Make sure the IP address is correct, make sure the port is correct. Click OK. Select the option to use SQL Server authentication and use the one from Enterprise Manager. Click next and see if you get a failure or another window. If another window, then it worked and you can save it and try your report.

-SQLBill

Posting advice: FAQ481-4875
 
Hi SQLBill,
Thanks again for helping me.
By Enterprise Manager, are you referring to the SQL Web Data Administrator?

I went into Security and added another Login and Password. The chart shows my Name, Type - Standard, ServerAccess - NonNTLogin, Language - English
I then went to Edit Login. The general section, Authentication is not accessable. default database is CE10, Language is English. Under Server and Database properties, I checked everything.

In DataSources(ODBC), Under UserDSN
Name: Enterprise
Description: SQL Server
Server to connect to: P4
(I tried entering the IP address, but I got an error message 17, server didn't exist)
next==>
SQL login (original login info)
Login ID: sa
password: password
Client Configuration:
Server Alias: Sharon
Network Library: TCP/IP
Server Name: P4
Port: 1433

System DSN
Name: CE10
Driver: SQL Server
Configure==>
Name: CE10
Description: Crystal Enterprise
SQL Server to connect to: Sharon (alias from User DSN)
next==>
SQL login (login I just created)
Login ID: SharonNiles
password: password
Client Configuration:
Server Alias: Sharon
Network Library: TCP/IP
Server Name: P4
Port: 1433

Both connections work. At the DOS prompt, OSQL -L shows (Local) and Sharon

Also both login and passwords are correct.

I am able to log onto Business View Manager as Adminstrator, but it won't accept either of my logins.

I appreciate your help.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top