In times past I connected my web app to SQL Server by embedding the following in the web.config file:
<connectionStrings>
<add name="XyzApp_DB" connectionString="Data Source=MyServerSQLExpress;Database=XyzApp;User ID=sa;Password=secret_password"/>
</connectionStrings>
And it worked fine. But it was pointed out to me that this wasn't a very proper way to do things. So I was advised to set things up with a different connection string:
<connectionStrings>
To get this to work, I've followed these instructions:
A
more secure login than “sa� is required to access your application.
In the “connectionStrings� section of
“Web.config� you’ll find a User ID called “Xyz_Admin�.
Here are the steps to ensure that your
DB has this login id:
a. Using
the Microsoft SQL Server Management tool, look in the root for the “Security�
folder and open it.
Inside that, open
the “Logins� folder.
b. If
“Xyz_Admin� doesn’t exist then right-click on “Logins� and choose “New Login�:
i.
Enter “Xyz_Admin� in the “Login name� textbox.
ii.
Click on “User Mapping� on the left side and in the
‘Map’ column check the box beside “XyzApp�.
iii.
Go back to the “General� page and choose SQL Server
Authentication.
iv.
Specify any secret password you wish.
v.
You can uncheck “Enforce password expiration� if you
wish.
vi.
Specify “XyzApp� as the default database and press
OK.
c.
Open “Databases�, right-click on “XyzApp� and
select “New Query�.
Run this query: sp_change_users_login 'update_one',
'xyz_admin', 'xyz_admin'
Now, I was pretty sure that this procedure was working fine but it doesn't seem to work for me today. I know that the client computer I'm using can connect to the DB on the server because I temporarily changed the User Id to "sa" and it connected fine. But when I change it back to "Xyz_Admin" it does not. So I'm thinking that there's something incorrect with the procedure above but I don't know what.
Any ideas?
Robert W.
Is it possible for me to login using windows login information that is separate from my current windows login? That is to say, can I login to a SQL server using windows athentication but actually specify the login and domain information? I woul like to login to a SQL server from a computer that is not part of the domain using my windows credentials and not a SQL user name.
I am running Windows server 2003, IIS 6.0, and SQL Server 2005. I am trying to set up dotnetnuke to run on the same machine as both these servers and I am running into an error that the dotnetnuke forum has not been able to help me with. I have set up a user called dnnuser in my windows authentication domain, and have added the user to SQL and given them ownershhip of my new dotnetnuke database. Howeever, when I try to start up DotNetNuke in my browser on the local machine I get a failure to log in. The first messages in theSQL Server log is "Error: 18456, Severity: 14, State: 6" then I get "Login failed for user 'DSD-WINdnnuser'. [CLIENT: <local machine>]" Then the next two lines in the log are "Login succeeded for user 'DSD-WINdnnuser'. Connection: trusted. [CLIENT: <local machine>]" and then "Login succeeded for user 'NT AUTHORITYNETWORK SERVICE'. Connection: trusted. [CLIENT: <local machine>]". What would cause the server to reject the login once and then immediately accept it?
I€™m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.} My windows Regional and Language options €“ English (United Kingdom), Sort date format is dd-MM-yyyy. When converting the date time in Sql server is using the mm-dd-yyyy format. But I€™m supplying the dd-mm-yyyy format date time.
I tried number of things none of them worked for me
1. Tried changing the default language and get the date time format - exec sp_configure 'default language', 2057 reconfigure - did not work EXEC sp_defaultlanguage 'my user name', 'British' - did not work (Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQL---conversion-errors-or-blank-pages__592__.htm)
2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057: [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsClientSetupCurrentVersion] [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsSetup [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1Setup] (Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891) - did not work
3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format --------- Did not work
4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work (Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )
5. Uninstall and reinstall SQL server express several time and did the steps 1 €“ 4 where applicable €“ did not work€¦.
If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates.... Please help me or point me in the direction in which I have to look for an answer. Thank you very much€¦.
Hi, Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword") Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn) Line 15: objConn.Open() Line 16: objRdr = objCmd.ExecuteReader() Line 17: While objRdr.Read()
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
select name,id,case when maths>=35 then 'pass' else 'fail' end as maths, case when science>=35 then 'pass' else 'fail' end as science, case when tamil>=35 then 'pass' else 'fail' end as tamil , case when english>=35 then 'pass' else 'fail' end as english, case when social>=35 then 'pass' else 'fail' end as social from student
I dont want to use case in my querey? Is there any way can i replace case with something? Thanks in advance!
I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?
hi i have a view in oracle. in that i am using decode function. same query i want to write it in sqlserver. what it is the alternate to decode.
this is a cross tab query
SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2, SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4 FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code
Is there an efficient scripting method to update the connection string for ALL reports that reside on a reporting/web server? "(automating the process, rather than having to change the data source for each individual report that resides on that server)".
-- tested schema below ---- create tables --create table tbl_test(serialnumber char(12))gocreate table tbl_test2(serialnumber char(12),exportedflag int)go--insert data --insert into tbl_test2 values ('123456789010',0)insert into tbl_test2 values ('123456789011',0)insert into tbl_test2 values ('123456789012',0)insert into tbl_test2 values ('123456789013',0)insert into tbl_test2 values ('123456789014',0)insert into tbl_test2 values ('123456789015',0)insert into tbl_test2 values ('123456789016',0)insert into tbl_test2 values ('123456789017',0)insert into tbl_test2 values ('123456789018',0)insert into tbl_test2 values ('123456789019',0)insert into tbl_test values ('123456789011')insert into tbl_test values ('123456789012')insert into tbl_test values ('123456789013')insert into tbl_test values ('123456789014')insert into tbl_test values ('123456789015')-- query --Select serialnumber from tbl_test2where serialnumbernot in (select serialnumber from tbl_test) andexportedflag=0This query runs quite fast with only the data above but when bothtables get million plus rows, the query simply bogs down. Is there abetter way to write this query?
Hi,I have a situation where I am loading data into a stagingtable for multiple data sources. My next step is to pick up therecords from the staging table and compare with the data in thedatabase and based on the certain conditions, decide whether to insertthe data into the database or update an existing record in thedatabase. I have to do this job as an sp and schedule it to run on theserver as per the requirements. I thought that cursors are the onlyoption in this situation. Can anyone suggest if there is any other wayto achieve this in SQL 2005 please.ThanksSeshadri
I am currently with sql 2k5 and using web assistant to generate some HTML files and email the same automatically to a set of users. these html files that are generated are processed via a JOB in sql server. I am in process of replacing of the web assistant procedures. Is this possible to make this with XQuery of SQL Server 2K5 and convert it into HTML files with Stored Procedure. Is there any other possible way to do it?
"Update STR_RGSTR_DFLT " & _ " Set GST_SVY_FREQ_P = " & iNewSurveyPct & _ " Where dhc_co_c = " & iCo & " and store_i = " & iStore & _ " And RGSTR_PROTO_C not in (0,3,4,8,10,11,12,13)"
in this the values in the where clause 'not in' i.e (0,3,4,8,10,11,12,13) are directly given but we want some alternate solution. the entire script is writen in VBScript. these values are already populated in an array like proto_code_ary = {0,3,4,8,10,11,12,13} shall we use that array directly without hard coding the values in the query?
Hi What is the difference between Candidate key , composite key and alternate key. I went through many websites but I didn't get examples. There were only definitions. Can anyone please tell me the site or blogs that elaborate this concept RegardsKaran
We just erected a new firewall that only allows PASV FTP. Now, my SQL Server can't call master..xp_cmdshell "ftp ...".
Does anyone know how to make the NT FTP client work in PASV (passive) mode or another FTP client that can be called from xp_cmdshell and works in passive more?
I need to join a remote table which has 20 million rows to few local tables on my SQL Server. Currently Linked server query is used but it's just hanging for ever..is there any alternative for this?
I have a production database with input from 3 locations outstation. I need to bring down this server once in a while for tuning and other administrative stuff. Right now I'm in no position to shut the server down. Is there any way I can do this.
Also how can I have 2 databases and toggle between the 2 seamlessly without effecting the users. Does it have to be a mirror on the devices ? What do the professionals usually do
I have heard about clusters. Is this a solution for my problem and what is a cluster anyway ? Vijay
Yes, I know synchronisation to alternate partners is deprecated in SQL2005 but....
In SQL2000 there is a Sync Partners tab in the publication properties dialog that allows you tick a checkbox for each co-publisher to be enabled as an alternate synchronisation partner. What is the equivalent in SQL2005?
I've set up replication in SQL2000 following these instructions http://support.microsoft.com/?kbid=321176 and it works. Now I'm trying to do the same thing in SQL2005 but I can't find a substitute for steps 10 & 11 in the section "Set Up the Alternate Synchronisation Partner". What's the answer?
I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments. As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists. Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way... FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup Thanks in advance for any help you can provide!
Hi: I created following temp table in a proc as create table #tempOutput (objID int identity (1,1) primary key clustered, DriverID varchar(10) not null, StatusID int not null constraint AK_tempOutput_DriverID_StatusID unique (DriverID, StatusID), Note varchar(50))
I run the proc first time ok. but the second time got the error: AK_tempOutput_DriverID_StatusID exists already....
after trace to tempdb ----- select * from tempdb..sysobjects where name = 'AK_tempOutput_DriverID_StatusID'
name id xtype parent_obj AK_tempOutput_DriverID_StatusID 849652640 UQ 833652583
select * from tempdb..sysobjects where ID = 833652583 Now, the temp table tempOutput becomes #tempOutput_____________________________0000000002 A1
But I could not drop this table in tempDB, and I could restart the SQLserver to clean the tempdb at this time.
after I renamed the AK_tempOutput_DriverID_StatusID to AK_tempOutput_DriverID_StatusID_1 in the create sql statement, the proc recreated and exec works.
Is there a way without restart SQL server to clean the Alternate key? Is there a way without restart SQL server to clean the temp table? Also, how to prevent the issue? This happens in both SQL2005 ent and SQL2000 ent. I think the exec proc ending should drop the #temp automatically and thus corresponding PK/AK?
For a backup strategy, I was thinking of just copying over all the system and user database device (DAT) files (of course after stopping all the SQL services at a time when no one is accessing the database server). Then at the time of restoration, I would copy the files back to their original location.
I have a Tabular model with a situation where I want to have three alternate attributehierachies in one dimension.
Dimension FruitAndVegetables (with 4 columns: Id, Name, Fruit and Vegetable) Id Name    Fruit     Vegetable 1 Apple     Apple   2 Onion                  Onion 3 Banana  Banana 4 etc
Now I would like to put Vegetable on rows in a report without getting a blank row (with the sales of all Fruits)..I would like to supress all those Fruit records without adding a separate filter to the report, just let the user pick this Attribute should do the move.
Is there a way to set up line shading on a report that is created from a table in SRS? The report is great but I want to shade every other line to make it easy to read and being a beginner with SRS I have not been able to find a way to do this.
Hi, I have transactional replication setup with SQL 2000 on W2K3 cluster using updateable push subscribers. While setting up replication, we chose default location for snapshot folder that resides on non-clustered drive. Is there a way to change this location without disturbing current replication setup. I looked at the 'alternate snapshot location' solution, but it requires snapshot re-initialization. I am trying to do this with minimal effort and downtime. Thanks, np70