Add user to MSSQL server 2012  for the web user linked to coldfusion

Posted on 2014-10-03
Medium Priority
Last Modified: 2014-10-11
I have migrated from SQL Server 2005 to SQL server 2012 and CF11.  A user was set up as the user in coldfusion in my old setup - that is the web visitor. I dont know the password for the old web user set up. I am a novice and this is a once in 12 years  for me -  SQL Server 2012 seems complex. I just want to add a user for the CF (web visitor) and it asks for a password. Cant even see where you add a password to the user. Anyhow appreciate some help So the webuser can read - write to db - register, update etc.

Question by:Ian White
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
  • 5
  • 4
LVL 52

Expert Comment

ID: 40362593
What is asking for a password - SQL Server or ColdFusion? Might be easier to post a screen shot.

Also, did you upgrade - or are these completely new installs?

Author Comment

by:Ian White
ID: 40362651
I upgraded from SQL server 2005 to 2012 via 2008 - migrating to a new host

Coldfusion needs a username and password for the public user (surfer) to login - it is where you define, verify the
DSN for the database. The user needs read and write access

Does anyone have the Transactional SQL so I can try and create a new public user with read and write access (not admin). Sorry could not do screen print from RDT - but here is a copy of labels on CF admin page

Data & Services > Datasources > Microsoft SQL Server
 Microsoft SQL Server :  housecare2  
 CF Data Source Name      
 Server        Port      
 User name    
LVL 52

Expert Comment

ID: 40362682
> Coldfusion needs a username and password for the public user

EDIT: Yes, I understand but it's not clear whether you really need to create a new user account.  That should only be necessary if it's completely new db.  If you upgraded an existing db, it would still contain all the old data AND logins.  So you wouldn't need to a new login account at all.  Just to reset the password of the old one.

If that's the case, just log into the SQL Server database with something like Management Studio.  Locate the login and change the password to something else.  It can also be done with TSQL:

      ALTER LOGIN surfer WITH PASSWORD = 'the new password you want here';

Then use the new credentials when creating the DSN in the CF Administrator.
Independent Software Vendors: 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 52

Expert Comment

ID: 40362707
> Does anyone have the Transactional SQL so I can try and create a new public user with read and write access (not admin).

I normally use SSMS for managing logins, instead of TSQL. So I'll let someone else offer up the TSQL.  But if you're interested in using SSMS it looks like 2012 hasn't changed much since 2008:

Add new login

1. Open Management Studio. Select Object Explorer > Security
2. Right click Logins > Add New
3. Enter the desired user name. Check "SQL Server Authentication" and enter a password
* Note: For web application accounts, you usually want to uncheck the last two options, ie "expire" and "change at next login"
create login and password4. Without closing the window, select "User Mapping" from the left menu. Check the box next to the name of your web database.
5. Under "Database role membership", check the boxes for the desired roles. Then click OK to apply.

* Note: For DEV db's I often use "db_owner".  For PROD you may want to assign different roles like db_datareader, db_datawriter, etc... All depends on what permissions your app needs.

assign login roleWhen done, verify the new password works in SQL Server. Then enter the new credentials in your CF DSN.


Author Comment

by:Ian White
ID: 40363206
No 2012 has changed .  It is right click on user then add new user - then SQL user with login - it asks for username and login name -    and there is no provision to put in a password.

I altered an old login password and it excecuted - then when I tried to add to coldfusion dsn verification I got
Connection verification failed for data source: housecare2
java.sql.SQLInvalidAuthorizationSpecException: [macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'house-pub'.
LVL 52

Expert Comment

ID: 40363566
I've gotten that error when updating an existing DSN -after changing the password. As if the credentials were cached somehow. It didn't stick until I edited the DSN a 2nd or 3rd time (changing the password - then clicking submit)

If it's a brand new DSN:

1. Did you verify you've enabled SQL Server Authentication in the db? Mixed mode authentication is NOT enabled by default.
2. Did you verify the credentials in SQL Server 1st?

Accepted Solution

Ian White earned 0 total points
ID: 40364811
Thanks. I finally got a response from the new hosting company. So it appears it something you need to be aware of when migrating? The DSN works now after below. It appears the login lives below the user - 2 entities.

In this case the issue was related to the "house-pub" user that was restored when the database was migrated onto the VPS server. Since the MSSQL server and MSSQL databases maintain their own user accounts and login, it turned out that the "house-pub" user that was created for the MSSQL server, was not linking to the "house-pub" user that was restored with the database. Hence when trying to access the database the login would fail. To correct this issue I ended up documenting the access rights granted to the "house-pub" user attached to the database, and removing all instances of the user account from the server, before adding it back and re-configuring the account with the proper user rights as you had previously setup.
LVL 52

Expert Comment

ID: 40365015
Ohh... that makes sense.  I'd forgotten about that. The same thing happened to me once when restoring a dump of another db into my DEV environment due to how SQL Server treats server level logins vs db users.  

Glad you solved it! Don't forget to mark your comment as the answer to close out this thread.

Author Closing Comment

by:Ian White
ID: 40374609
Thanks for your contributions, as it helped me get to a solution

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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