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
  • 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.
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.

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now