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

Posted on 2014-10-03
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.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authoriā€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

724 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