Solved

Coldfusion Administrator will not register a SQL 2000 datasource.

Posted on 2003-11-08
3
252 Views
Last Modified: 2013-12-24
I hope someone is up and can help me out real quick.  I am trying to convert my site from sybase to MS SQL 2000.  I can only get my administrator to connect to the database with the sa user and no other user I have created.  What am I doing wrong??  Also, how do I address calling the tables in the query when I do get it connected??
0
Comment
Question by:benjam222
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
shooksm earned 500 total points
ID: 9709046
Are you using mixed authentication?  That is both SQL server accounts and Windows Authentication to validate users.  If that is the case, make sure you are not trying to login with a Windows account.  If you need to use windows authentication, then I think you will have to use the same windows account used to run the Cold Fusion service and give it rights in SQL.

Another thing is to check the login.  Use query analyzer and see if you can connect to the server, select a database and run a simple query.

There are several steps in creating logins and giving the login the proper access to do anything.

1. Create a new user
2. Select the authentication mode
3. Fill in the password if necassary
4. Select the default database for the account
5. Select the third tab.
6. Check the permit box for the default database
7. Select the database
8. Select an appropriate role for the user like datareader and datawriter
9. Click OK to save

As for how to you call specific tables, there are a couple of ways.  First, the accounts default database will be used when you don't specify an alternate database in your CFQUERY tag.  So, you can get away with accessing the tables directly IE:

SELECT *
FROM SomeTableInMyDefaultDatabase

Second, you can specify the database to use in the database parameter of the CFQUERY tag.  Third, you can use fully qualified names to choose a table outside of your current context. IE:

SELECT *
FROM SomeOtherDatabase.dbo.SomeTableInAnotherDatabase

The fully qualified syntax is

SERVER.DATABASE.OWNER.OBJECT


Hope this helps
0
 
LVL 4

Expert Comment

by:procept
ID: 9709419
Hi,

in SQL Server you need to assign the specific user rights to use the database and maybe even rights to SELECT, INSERT, UPDATE, and DELETE receords.

In Enterprise Manager, expand the SQL Server Registration and click on "Security".

If your user is not listed in there, create a new one by right clicking in the window and choosing "New user". Assign that user a name, set his authentication to SQL Server authentication and type in his password. Click OK and re-enter his password.

Double click on the user name you wish to use, click on the "Database access" tab and make sure that all databases he should have access to are checked. Click OK.

Now the user has access to the database. Next you need to give him permissions to select, insert, update, and delete records. You can do this manually in Enterprise Manager (which actually is easy, but, takes long if you have many tables), or, you can use the stored procedure "sp_MSforeachtable" that comes with SQL Server.

Execute this command as sa in Query Analyzer (make sure you are connected to the correct database!):
EXECUTE sp_MSforeachtable 'GRANT SELECT ON ? TO your_usename_here'

Now you can use this user and his password to use the DB with CF.

HTH,

CHris

0
 
LVL 17

Expert Comment

by:anandkp
ID: 9713080

If u have got the logins & roles defined properly in MS SQL 2K - then u shld be able to connect.

chk ur settings in MS SQL 2K & try again - u shld be able to connect thru all users [else whats the point in creating them :)]

K'Rgds
Anand
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

627 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