SYSTEM_USER And Sql Server Authentication - Not Working?
May 8, 2008
Hi,
We just restored our database from SQL Server 2000 to SQL Server 2005 for me to test things out. Right off the bat I started seeing errors where I could not select data from a table. I would get the "Invalid object" error unless I specified the schema of the object. I found this confusing because I was connected to the server using SQL Server Authentication as the schema_owner ( i.e user name and schema name were identical). I thought this was due to the fact that my DEFAULT_SCHEMA was not set but when I checked, user 'testuser' had schema 'testuser' as default_schema. I also made sure I was connected to the Database using SQL Server Authentication as user 'testuser'.
But the following would fail:
SELECT * FROM Employees
The following would however succeed.
SELECT * FROM TestUSer.Employees
I was really confused and when I read some posts and forums, I decided to check SYSTEM_USER and to my dismay, it returned my Domain login as the value as opposed to "TestUser". I re-connected using my Domain Account and SYSTEM_USER returned the Domaim Login as expected but when I switched to Sql Server Authentication and connected as user "testuser", SYSTEM_USER would still return my Domain Account. I believe this is the source of my problems. Can you tell me what's going on.?
I am a member of the sysadmins group and CURRENT_USER returns DBO. Please help as this is driving me nuts!
I have setup ODBC to connect to a SQL Server 200 database (evaluation version) so I can link tables into an Access database. In the ODBC setup, I selected SQL Server Authentication versus Windows Authentication. However, when I link a table into Access it still uses Windows Authentication first.
I found an article here that discusses the issue: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q279526
It suggested that I run an MDAC update to version 2.6. So I did that and I still have the same problem.
I tried to link a table through some code in Access by changing the connect string to "Trusted_Connection=No" but it still linked up with "Trusted_Connection=Yes".
Hi, I need a DELETE trigger to log the user who actually deleted the item in Sql Server. Problem is that I am using connection pooling on my SqlClient side of things and so all users share the same connection string user id.
Now, I need to be able to access both the connection_user id (otherwise known as system_user) and also the user on the executing thread on the client. I do this in Oracle and DB2.
So, can you tell me how to pass this info into the connection and how to extract it within Sql Server. ThxP
Hey there, I have a table [user] in a SQLExpress database [Database.mdf] in the App_Data folder. There are two columns. [userId] and [password]. I want to authenticate the user who wants to log in through a Login control. the user should have his userId and password in the [user] table. I digged and made my code. but it doesn't work it keeps giving me that the user is not authenticated. i guess it has no errors. please tell me if you find the error. here is the code: 1 protected void Login1_Authenticate(object sender, AuthenticateEventArgs e) 2 { 3 SqlConnection conExpress = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Database;Integrated Security=True;User Instance=False"); //Create the server connection 4 5 try 6 { 7 string id_user = Login1.UserName.Trim(); //Get the username from the control 8 string pass_user = Login1.Password; //get the Password from the control 9 10 //These are a sql and connection Examples 11 string sql = "SELECT userId, password FROM user WHERE userId = @param_Id AND password = @param_Password"; 12 SqlCommand comSQL = new SqlCommand(sql, conExpress); //Create the sql command using sql string and sql connection 13 14 //Add the sql parameters 15 comSQL.Parameters.AddWithValue("@param_Id", id_user); //New on VS 2.0 16 comSQL.Parameters.AddWithValue("@param_Password", pass_user); 17 18 string cod_user = ""; 19 string name_user = ""; 20 21 //Open database connection 22 conExpress.Open(); 23 SqlDataReader dr = comSQL.ExecuteReader(); 24 while (dr.Read()) 25 { 26 cod_user = dr.GetValue(0).ToString(); //The coduser is unique onto database table 27 name_user = dr.GetValue(1).ToString(); 28 } 29 conExpress.Close();//Close Database Connection 30 if (cod_user != "") //The user exist onto database 31 { 32 //Create the session vars 33 Session["coduser"] = cod_user; 34 Session["nameuser"] = name_user; 35 e.Authenticated = true; //Grant the access, Goes to DestinationPageUrl 36 } 37 } 38 catch(Exception)//On Login Error 39 { 40 e.Authenticated = false;//Confirm that you are out 41 conExpress.Close();//On any error case, close the database connection 42 } 43 }
I am trying to access the SQL server using windows authentication but its not working. But others can work on their windows authentication for the same server. Anything needs to be done from my end. Please explain.
When logged onto a server and connecting using windows authentication (either by server name or localhost) I am getting in fine, but when I connect using the IP I get the following error.
I can log in using SQL Server authentication with the IP.
This morning I attempted to connect to my local copy of SQL Server (2012) (Win 7) using Windows Authentication. I was unable to do so and per the error log it was due to error 18456 (invalid credentials).
Yesterday I changed my windows password. Is it possible for this to be the cause?
FYI, following is from the error logs:
2015-04-27 10:10:32.98 Backup BACKUP DATABASE successfully processed 0 pages in 0.451 seconds (0.000 MB/sec).2015-04-28 00:00:41.90 spid18s This instance of SQL Server has been using a process ID of 2096 since 4/21/2015 9:08:05 AM (local) 4/21/2015 1:08:05 PM (UTC). This is an informational message only; no user action is required.2015-04-28 09:58:51.16 Logon Error: 18456, Severity: 14, State: 38.2015-04-28 09:58:51.16 Logon Login failed for user 'NT AUTHORITYSYSTEM'. Reason: Failed to open the explicitly specified database 'model'. [CLIENT: xx.xx.xxx.xxx]2015-04-28 17:09:59.87 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
The logs showed the error occurred yesterday (4/28) which is about when I changed my password. The 17:09 log (last one) is when I shut down my computer. Note that I was logged into SQL Server when I changed my Windows password and worked with it several times through out the day.
Also, per the configuration manager SQL Server (MSSQLSERVER) (and all other services) is stopped and I was unable to start it ("The request failed or the service did not respond in a timely fashion).
Trigger works great, except for one problem. ALTER trigger [dbo].[Loans_UpdateTrigger] on [dbo].[List] for Updateas insert into Loan_Audit select *, Getdate(), system_user, 'Update' from Deleted The system_user returns "DomainNameWEB$ instead of the actual users name. Any way to get the actual users name??? thanks Chuck Snyder
DECLARE @UserName nvarchar(100)SELECT @UserName = SYSTEM_USERvalue returned is "DomainNTSignonName"What I want is only "NTSignonName"Is there a function to do this or an easy parse for this in SQL2000?lq
I use asp.net 2.0 to access a SQL Server Express. I want to record the user who modify the database, so I create a trigger as follows: BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here UPDATE Customer SET UpdateUser = SYSTEM_USER WHERE ID = (SELECT ID FROM inserted)END In IIS ,I configed to use Integrated Windows authentication, The problem is when I modify the database, UpdateUser always setting to NT AUTHORITYNETWORK SERVICE. How to record the user as the format DomainUsername? Thanks, Alvin
I know that system_user can return different types of usernames based on the authentication method used to connect to the database. I am trying to nail down a standard field width for audit columns that store the return value from system_user but I can't find definitive information about the return type of the function. Does anyone know the maximum length of the return value from the system_user function and if its an nvarchar, varchar, nchar, etc.?
For using different services of SQL SERVER 2005 which is better... Windows Authentication or SQL Server Authentication? what are the advantages and disadvantages of both?
I wonder if it is possible to set forms authentication for report manager but leave report server "as it is". I need to authenticate users from external LDAP and can't use windows authentication for report manager, but I would also like to leave report server open for anonymous users. In that way authenticated administrators could create reports which anonymous users could read.
I tested the Security Extension Sample and got it working when I rewrote the authentication part with my own LDAP authentication.
If I have understood correctly, the report manager is just application inside report server so is it possible to use forms authentication with one application but still leave the report server with Windows authentication?
I need to figure out what kind of Authentication , I need to use for following applicaiton
Product : -
1 ) It resides on a its Domain and has access to Database on that Domain. 2 ) We have a application level login , n based on application login id display specific pages.
The question that bother me is this
Q ) If i use NT authentication , then a user will be required to
a ) Login to domain (with userid and password) first and then
b ) Then i would require to again login to applicaiton with application level login and password.(different levels of login as there) Based on the application level login i will display only specific asp pages. They have different access rights..and roles.
Requirment is to login only once..and it should authenticate to application display specific pages and authenticate to SQl server database also..
Is there any way thru which i can map my application level login to SQl server.. and what authentication should i use..
I folks.I Have installed sql server 2005 express and choosed windowsauthentication on instalation, but i make a mistake and now i needmixed authentication, how can i modify this whithout uninstall andinstall again the application?thanks for the help.
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
(Using win2k, sqlserver2k, framework 1.1) I have an fairly data-heavy application that uses Windows authentication (Trusted connection/aspnet account) to connect to Sql Server. The site uses IIS basic authentication.
On the dev server everything works fine but when I move to the live server things get strange and it starts to crawl along. (Pages load OK but then it just crawls as it loads the datagrids etc. Sometimes it brings back incomplete/incorrect data )
BUT When I use Sql Authentication to connect to Sql Server and there is no problem at all!
Ok, there is something obviously wrong with the live server (which is identical setup to dev)but I dont know where to start.
I've got two applications which both have a database on my MS SQL 2000 server. The problem is, one application must use Windows Integrated Authentication (which it is currently using and cannot be changed) whilst the other application which I'm trying to configure must use a SQL password.
Since the server has already been configured to use Windows Integrated Authentication for the existing database and application, how do I configure the other database to use the SQL password?
My work is using a shared application which accesses a MSSQL 2000 database. To access the application, the folder on the Windows 2003 Server is shared and users can access the folder through a shared drive.
For the application to access the database, it uses an ODBC connection to the MSSQL server which originally used the SA password.
We have recently switched to using Windows Integrated Authentication because we believe it offers a higher level of security. However the only way in which we have been able to enable this is to add the windows users to the SQL server.
The problem with this is that the application sets permissions for individual users on what records they can see within the database. We have found that by adding the windows users to the SQL Server, they can bypass the permissions the set by the application by simply using any application that can use an ODBC connection, such as Enterprise Manager, and see all the database.
One way around this would be to set up domains of users with access privileges to the tables which reflect the permissions set by the application, and configuring a view of the data so they may only see the records that they have permissions to. However to do this would require a high administrative cost to ensure that changes made in the application are reflected in the privileges of the SQL server.
Instead, is there a way the SQL server can authenticate that the ODBC connection is coming from the correct application using Windows Integrated Authentication?
This would allow the applcation to determine security, and stop users from connecting to the SQL server using other applications.
Alternatively, can the SQL server, using Windows Integrated Authentication, also ask the application to supply a username and password?
Any help with this matter would be greatly appreciated.
Hi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
Say, I have configured my SQL to use Mixed Authentication. Now, I have a applicaiton which uses my SQL Server. The application just creates a database in SQL Server and uses the database to store its information.
This application also has a SYSTEM DSN under ODBC through which it accesses the database. For the application to access this database, should I only use SA (as my SQL instance is configured to use Mixed Authentication) or can I use Windows Authentcation too...
If I should only use SA, do we have a documentation which talks about this.
I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered: "[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".€œ
I have created an EndPoint in SQL Server 2005 as per the code below.
CREATE ENDPOINT OSTC_LMS_Endpoint
AUTHORIZATION LMSEndPointUsers
STATE = STARTED
AS HTTP(
PATH = '/ostc_sql_endpoint',
AUTHENTICATION = (BASIC),
PORTS = (SSL),
SITE = 'OSTC-DEV-001'
)
FOR SOAP (
WEBMETHOD 'ostc_SQLSoapTester'
(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',
FORMAT = ROWSETS_ONLY,
SCHEMA=STANDARD),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'OSTC_LMS_06',
NAMESPACE = 'http://tempUri.org/'
)
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [LMSEndPointUsers]
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [ostc-dev-001endPointUsers]
GO
----------------------------------------------------------------------------------------------------------------- The SPROC being exposed as the webmethod: -
The computer in question is our dev server and is running as a workgroup machine with the following: - Win Server 2003 SQL Server 2005 .net Framework 2.0 No firewalls or Proxies are in the way.
The computer has to be as a workgroup machine to reflect our live server.
The user LMSEndPointUsers is a SQL Server Login The user ostc-dev-001endPointUsers is a machine login
We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.
I have 7.0 server running with sp2 on NT box sp 5. This Sql server is configured to take both sql and NT authentication. Somehow, the bridge between NT and SQL server doesn't seem to be working as sql server does not recognize any NT authentication. Is there any way to fix it?
Because of this problem, I am unable to schedule any DTS package as sql server security doesn't recognize the sqlagent startup account and gives a login failure error.
I am about to open a ticket with Microsoft but before that if anybody can suggest me anything..
I have an SQL Server (2000 Edition) that does not belong to a domain; Server A. (It loggs onto its own computer domain). From this server I would like to add another sql server registration through Enterprise Manager that does belong to a domain; Server B (also 2000 Edition).
The only way I can register Server B on the Server A computer is by logging onto the domain that Server B belongs to.
Is it possible to just use SQL Server authentication and not have Server A logon to the Server B domain? If so what could I be doing wrong?
When Server A is logged onto its own local computer domain I can ping Server B and also resolve the server by name.
I can currently log in through Management Studio using Windows Authentication. I want to login using my sa account (which exists) and a password, and eventually be able to do role based security for additional users.
Hi ,am new to sqlserver 2000.I want to connect to my sqlserver which is aremote machine.I want to connect to sqlserver by sqlserverauthentication.Even though i added the available sqlserver in myclient as it shows, it is connected only by windows[nt)authentication.Why it is showing so ?.* But it is connected via Query analyzer in sqlauthentication.I want to connect this sqlserver to my asp.net .Is the connection string as follows is right ,if no pls correct me,---------Persist Security Info=False;IntegratedSecurity=SSPI;database=NORTHWIND;server=IISSERVER; User id=sa"---------It shows the error .Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.I ve'nt mentioned any authentication mechanisym in my query string..From the error it defaultly accepts the NT authentication.How can iover ride this to sql authentication.Pls tell me the procedures should be made to connect to sqlserver insql authentication.Thx & RegardsRaghu
I would like to add a new data source to my windows application using SQL server authentication. When I made the connection it did not have any problem. When I add the tableAdapter control it give me the message:
