T-SQL (SS2K8) :: How To Create Login For Client App
Jan 30, 2015
I have a client application written from C#/winforms app that needs access SQL DB on SQL server 2008 R2 developer. So I need to create a Login for this app with a user/password. But when i created it and put it on the client's connection string, the DB could not be accessed by the client windows forms app and the error was "Login failed for User App1".
Then I tried to login to SQL server from SSMS using the credentials of App1 but it also gave same error "Login failed for user App1". I have following the following steps of creation of the login for App1. i could login from "sa" account and other Admin accounts but not from "App1" account
1. created a server level Login
2. created a DB level user
3. created a Role (a DB role), i even tried creating an App Role
4. given permission to execute stored procedures that handle login
But didn't work. Maybe i made some mistakes in those step. So, how to create an account for user "App1" so it can be accessed by the client windows app and also from SSMS? i would prefer to use account creation from SSMS GUI.
How do i install SQL server 2008R2 in local machine with network accessibility features?
The reason I want to put an ip address on connection string i just want to create connection strings as people do in their work. When the app tried access the connection string it gives the error in the attachment.
Because I haven't used this user/password for a long time I'm pretty sure the user name and password is correct but i have the following questions
1. Assume the user and password is incorrect, is there way to reset this? 2. what other configuration that i need to do in order to make teh C# app access this Database?
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
I was told that the authentication is a process that concerns about security. So the authentication process should be hidden from the client as possible and the client should receive a flag value (1= password is correct and authenticated, -1= incorrect password) and/or user name of the authenticated user. So it should be a SQL operation that should occur in the back end. So the process in detail should be, client form sends user name and hashed /encrypted password to SQL, SQL authenticates and it sends 1/-1 with the user name to the client as the return value and the output.
But when i do research that was not the case, all the threads i have seen on web, either the app is windows or web, developers do two operations:
1. the salt value of the user is searched by user name then return the salt value along with the PasswordHash to the client form / or to the business layer
2. Then use the entered password and the returned salt value to generate a PasswordHash and then compare this passwordHash with the returned passwordHash. if match password is correct if not password is incorrect.
Is this actually how developers write code to authenticate a user in a login event? If so basically the password comparison happens at the client side(either in the form or in the business layer) then wouldn't this be huge security risk by exposing the actual salt and passwordHash to the client?
hi all, have anyone else met with the following problem.we've got a SQL2000SP3a on a 2 active-active node clusters of W2K3,this cluster in using AD.I've found that whenever I use Enterprise Manager on my local computerto register the above instance, it will constantly produce "Failedlogin for <Domain><DomainUser>" errors in the Windows Security Log ofthe database server machine. There's no failed login in the SQL errorlog and I can work normally with the Enterprise Manager. The OS failedlogins error are gone once I un-register the instance from myEnterprise Manager.Does anyone know or met with this before?
I used to have an Access database which had tables for users, roles, actions etc.
This was used by a C++ client app (using ADO) which logged in, got the user ID and password (by raising a login dialog ) and then checked these against a user table and then assigned the roles and possible actions.
Now we have SWL Server Express 2005 - NT Authorisation - how do I get/pass the user ID to the C++ Application so it can get the associated roles? Seems silly to have 2 logins.
Better still can I do away with the App's User table or make its password column invisible to all users bar Admin and the C++ App?
One of my co-worker changed the sa password today. After that we are getting this error. The SA account gets locked every time. Even within few seconds of unlocking the SA account, it gets locked again. Error message is not clear as what session still using old password and causing the SA account to get locked? Any of our application does not use SA user to connect to databases. We have Tivoli installed on the same system and the GUI of tivoli has the correct password.
I unable to setup a linked server on sql server 2012. The destination server is on sql server 7.0. I can able to setup the same linked server on sql 2005 sp4 and sql 2008 r2 but unable to setup on 2012. I am getting the below error message. OLE DB provider "SQLNCLI11" for linked server "" returned message "Client unable to establish connection due to prelogin failure".
I am getting the below error while installing CSF DevLite edition on
Following is the breif Info
Server Is : Win2003
SQL SERVER 2005 Installed on Same PC
Domain Name is : Test
User : Administrator he is a build in Admin,
PLEASE HELP ME!!!! Thanking in advance.
EVENT LOG 1:
Login failed for user 'TESTadministrator'. [CLIENT: <local machine>]
EVENT LOG 2:
The description for Event ID ( 11609 ) in Source ( MsiInstaller ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Product: Microsoft Connected Services Framework 3.0 Developer Edition Lite -- Error 1609.An error occurred while applying security settings. Power Users is not a valid user or group. This could be a problem with the package, or a problem connecting to a domain controller on the network. Check your network connection and click Retry, or Cancel to end the install. Unable to locate the user's SID, system error 1332, (NULL), (NULL), (NULL).
I just did a fresh install of SQL Server 2005 Developer Edition and SP1. I elected Windows auth during install. After rebooting I get 2 errors in my Application Event Log: MSSQLSERVER Login failed for user 'COMPUTERuser'. [CLIENT: <local machine>] (event ID 18456) and Report Server Windows Service (MSSQLSERVER) (event 107) which says: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. In the data section it says it can't connect to the machines "master" database. I can manually login. How can I fix the problem and get rid of these messages? Thanks. Mark
Maintenance plan for bakcup is failing with "Login failed for user 'sa'. [CLIENT: <local machine>]"
I went to the Maintenance Plan and opened the Subplan. I clicked the "Manage Connections"
It has three tabs:
Name: Local Server Connection Server: prod Authentication: SQL Server Authentication
I clicked the Edit and it shows the Connection Properties:
It says: Enter information to logon to the server. "Use a specific Username and Password" is checked. Username is set as "sa" while the password is empty. I typed in the correct password and pressed Ok. When I go back again, the password still shows empty. I tried to run the plan and it again fails. Do you know why it is not showing the password as blank even if I try to save the password.
Is there a way to extract (and copy to a network drive) the necessary files from the SQL Server 2000 CD for the purpose of a creating a client install directory? I would like to invoke the Setup from the network. I do not want to place the whole CD on the network. I do not necessarily want to have to re-key the serial number (product key) every time a client install takes place.
HELP! I'm new to SQL Server and I'm sure this is a simple problem. But I can't seem to solve it. Here's the problem: My website & MS SQL 2000 Server are being hosted by Networksolutions. Thru the Client Enterprise Manager I am Unable to create a New Table. This is the error message I get: " [MS Design Tools]-ODBC error: [Microsoft] [ODBC SQL SERVER DRIVER] [SQL SERVER] SELECT permission denied on object 'sysobjects', database 'namedb', owner'db' " :confused:
i connect to remote Integration Service. i configure server for remote connection( on component services and DCOM config , ... ) .and now i can connect to Integration Service remotely and correctly.
but when i expand Stored Package and then click to expand MSDB this error will hapen:
login failed for user ... .(microsoft sql native client )
I am trying to link tables from an access 2003 frontend to sql server 2005 backend.
I am doing this in a vmware test environment. I am using vmware server and running sbs 2003 and xp sp2 client. Both virtaul machine can talk to each other(using local host connection).
I have tried to create a dsn to the sql server but i cannot connect. I can ping the sbs server through the command interface but the error i get when i try to connect is :
Connection failed: Sql state: hyt00 sqlserver error: 0 microsoft odbc sql server driver timeout expired.
spent a day trying various combinations - still no joy.
Hello all, I have a database that supports two different applications. For arguments sake I'll call them "intelligent app" and "clueless app"
The client that provided me the schema for the clueless app used reserved words as field names. The words used are: value, state, and time
I explained to the client that modifying them could prevent current/future issues.
The client is an "oracle" db (and in my mind should know better but .....) and responded back to me with the following:
BEGIN QUOTE They are not currently reserved and there is no guarantee they will ever be reserved. In fact, the SQL server line eliminates as many words from the reserved list as they add with new releases. There are other words on the possible future reserved keyword list that I do not wish to avoid either unless forced to some day, like depth, size, class, zone, level and others. END QUOTE
Now for the assistance request, where can I find the documented ramifications of using reserved words? I want to have my documentation (and my ducks lined up) when this clients portion of the app fails.
I am trying to create several Excel sheets using SQL 2000 views like so:
Select * INTO [Excel 8.0;Database="C:spreadSheetsaNew.xls"] FROM [aView].
When I try and execute this in my app I get the following - Specified owner name 'Excel 8.0;Database=c:spreadSheetsaNew.xls' either does not exist or you do not have permission to use it.
If I use the above Select statement with an OLEDB connection it works.
I am using Imports System.Data.SqlClient, instantiating a new SQlConnection object, opening the connection, etc..
Error: Ole db error 0x80004005 Client unable to establish connection
This happens when trying to create a maintenance plan in sql 2005 sp 1
It has now happend with 3 installs - one fresh install and two upgrades from 2000 to 2005. The startup account for sql server is a domain user account and has not been changed. This happens on both windows 2000 and windows 2003.
This however does not happen if the sql startup account is in the local admin group for the machine
I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.
I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.
I want to create sp as like execute this way Exec @MICHELIN_US_
So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_
And it will change all table names to UNITED_ACTIVITY
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON; BEGIN TRANSACTION; DROP SYNONYM [dbo].[some_proc]; CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc]; GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public; COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092, Source=Microsoft OLE DB Provider for ODBC Drivers, Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Any way to prevent it besides a client-side retry?
I would like to create an index with a few options but having issues trying to parse and run it successfully. The issue is near the end of the statement. Here's the create statement below:
-- Index PSAPSACTIVITYDEFN on table PSACTIVITYDEFN IF EXISTS(SELECT 1 FROM sysindexes si INNER JOIN sysobjects so ON so.id = si.id
I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic. i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.
yesterday i was trying to create Stored procedure but it fails i don't know why
CREATE proc GetBooksbyBorrowerID @Borrower_id INT AS BEGIN SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b WHERE a.borrower_id=@Borrower_id AND a.ISBN = b.ISBN END GO EXEC SP_Task1 10001
I want to create column name dynamically in select list.
create table #temp(name varchar(10), sale int) insert into #temp values('john',1000) insert into #temp values('Mike',500) insert into #temp values('Abhas',200) select name,sale as sale from #temp
Now i want change column alias only , not value. I need to concatenate sale with year value of getdate(). i.e. column header concatenation only, not a output value.
I have a Table with Financial Data in it and for Certain Accounts (the Key Field is actindx Column)there is no data for Certain Months.
I need a query to fill in the data for each Calendar_Month. This should look at the actindx column and Calendar_Month column if there is no data for a specific Month for that actindx I want it to copy all columns for that actindx and insert into a new row, but just puts Zero Dollars for ActivityDebit, ActivityCredit, and NetAmount.
I have created a CalendarMonth_Lookup Table. I assume there is a way to outer Join the two and insert rows or use an "IF" ,"THEN" type of Statement and just manually add the Calendar Month data.
I am including all the code to make the tables and Insert Data into them.
